﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.SqlClient;

namespace CommClass
{
    class App
    {
        public static int pagesize = 20; //每页记录数
        public static int newstatus = 1; //新增状态
        public static int guanbi = 3; //关闭状态
        public static int qianhestatus = 4; //签核状态  
        public static int newstatusout = 0; //新增状态
        public static int notfreeze = 0; //非冻结 
        public static int freeze = 1; //是冻结
        public static int caigouputong = 1; //普通采购 
        public static int yueku = 2; //越库

        private static User appUser;
        public static User AppUser
        {
            get { return appUser; }
            set { appUser = value; }
        }

        private static string stIniFileName = "DMS.ini";
        public static string IniFileName
        {
            get { return stIniFileName; }
        }
        private static string stAppPath = Application.StartupPath;
        public static string AppPath
        {
            get { return stAppPath; }
        }
        //最大拣货数量，超过此数量的货物直发
        private static readonly double MaxPickQty = 400;
       
        
        private static List<string> messages=new List<string>();
        public static List<string> Messages
        {
            get { return messages; }
            set { messages = value; }
        }

        private static DBSource ds;
        public static DBSource Ds
        {
            get { return ds; }
            set { ds = value; }
        }
        public static SqlConnection Con = new SqlConnection(sqlconnection);
        public static string sqlconnection = GetSqlConnection();
       
        
        #region 常用函数

        /// <summary>
        /// 判断字符串是否为整数格式
        /// </summary>
        /// <param name="value">字符串</param>
        /// <returns>是返回true，否返回false</returns>
        public static bool IsInt(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?[0-9]+$");
        }

        public static bool IsNumeric(string value)
        {
            return Regex.IsMatch(value, "^(-?//d+)(//.//d+)?$");
        }
    #endregion

        #region 用户
        /// <summary>
        /// 判断用户代码是否存在
        /// </summary>
        /// <param name="UserCode">用户代码</param>
        /// <returns>存在返回true，否则返回false</returns>
        public static Boolean UserIsExist(string UserCode)
        {
            string strcmd;
            strcmd = "select * from t_user where UserCode='" + UserCode.Trim() + "'";
            DataTableReader dr = ds.GetRecord(strcmd).CreateDataReader();
            if (dr.Read())
                return true;
            else
                return false;
        }

        public static int UserGetMaxID()
        {
            int MaxID;
            string strcmd;
            strcmd = "select Max(UserID) from t_user";
            DataTableReader dr = ds.GetRecord(strcmd).CreateDataReader();
            if (dr.Read())
            {
                MaxID = Convert.ToInt32(dr[0]);
            }
            else
            {
                MaxID = 0;
            }
            return MaxID;
        }

        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static Boolean UserAdd(User user)
        {
            string strCmd;

            strCmd = "insert t_User (UserID,CompanyID,UserCode,UserName,UserPasswd,UserDescription,UserLevel,IsDisable) values (" +
                (UserGetMaxID() + 1).ToString() + ",'"+user .CompanyID +"','" + user.UserCode + "','" + user.UserName + "','" + user.UserPassword + "','" +
                user.UserDescription + "'," + user.UserLevel.ToString() + ",0)";
            if (ds.ExecuteSQL(strCmd))
                return true;
            else
            {
                messages.Clear();
                messages.Add(ds.ErrorMessage);
                return false;
            }
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="password"></param>
        public static Boolean ChangePasswordByCode(string userCode, string password)
        {
            string strCmd;

            strCmd = "update t_user set UserPasswd='" + password.Trim() + "' where UserCode='" + userCode.Trim() + "'";
            if (ds.ExecuteSQL(strCmd))
                return true;
            else
            {
                messages.Clear();
                messages.Add(ds.ErrorMessage);
                return false;
            }
        }



        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="updUser"></param>
        /// <returns></returns>
        public static Boolean UserUpdateByCode(User updUser)
        {
            string strCmd;
            int isDisable;
            if (updUser.IsDisable)
                isDisable = 1;
            else
                isDisable = 0;

            strCmd = "update t_user set UserName='" + updUser.UserName + "',UserDescription='" +
                updUser.UserDescription + "',IsDisable=" + isDisable.ToString() + " where UserCode='" +
                updUser.UserCode.Trim() + "'";
            if (ds.ExecuteSQL(strCmd))
                return true;
            else
            {
                messages.Clear();
                messages.Add(ds.ErrorMessage);
                return false;
            }
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <returns></returns>
        public static Boolean UserDeleteByCode(string userCode)
        {
            string strCmd;
            ///todo: 还需要删除t_UserRight表中相关记录
            strCmd = "delete t_user where UserCode='" + userCode + "'";
            if (ds.ExecuteSQL(strCmd))
                return true;
            else
            {
                messages.Clear();
                messages.Add(ds.ErrorMessage);
                return false;
            }
        }

        public static List<User> UserGetList()
        {
            List<User> UserList = new List<User>();
            string strCmd;
            strCmd = "select * from t_user order by UserCode";
            DataTableReader dr = ds.GetRecord(strCmd).CreateDataReader();
            while (dr.Read())
            {
                User tmpUser = new User();
                tmpUser.UserID = Convert.ToInt32(dr["UserID"]);
                tmpUser.UserCode = dr["UserCode"].ToString();
                tmpUser.UserName = dr["UserName"].ToString();
                tmpUser.UserLevel = Convert.ToInt32(dr["UserLevel"]);
                tmpUser.UserDescription = dr["UserDescription"].ToString();
                tmpUser.IsDisable = Convert.ToBoolean(dr["IsDisable"]);
                UserList.Add(tmpUser);
            }
            return UserList;
        }

        public static User UserGetByCode(string usercode)
        {
            User tmpUser = new User();
            string strcmd;
            strcmd = "select CompanyID,UserID,UserCode,UserName,UserLevel,UserDescription,IsDisable from t_user where UserCode='" + usercode.Trim() + "'";
            DataTableReader dr = ds.GetRecord(strcmd).CreateDataReader();
            if (dr.Read())
            {
                tmpUser.UserID = Convert.ToInt32(dr["UserID"]);
                tmpUser.UserCode = dr["UserCode"].ToString();
                tmpUser.UserName = dr["UserName"].ToString();
                tmpUser.UserLevel = Convert.ToInt32(dr["UserLevel"]);
                tmpUser.UserDescription = dr["UserDescription"].ToString();
                tmpUser.IsDisable = Convert.ToBoolean(dr["IsDisable"]);
                tmpUser.CompanyID = Convert.ToInt32(dr["CompanyID"]);
            }
            else
            {
                tmpUser = null;
            }
            return tmpUser;
        }

        public static Boolean UserCheck(string userCode, string Password)
        {
            string strcmd;
            //防止SQL注入式攻击
            if (userCode.IndexOf("'") >= 0 || Password.IndexOf("'") >= 0)
                return false;

            strcmd = "select count(*) from t_user where UserCode='" + userCode + "' and UserPasswd='" + Password + "'";
            DataSet Dat = ds.GetRecord(strcmd);
            DataTableReader dr = Dat.CreateDataReader();
            dr.Read();
            if (Convert.ToInt32(dr[0].ToString ())> 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        public static int UserGetIDByCode(string userCode)
        {
            int userID;
            string strcmd;
            strcmd = "select UserID from t_user where UserCode='" + userCode + "'";
            DataSet Dat = ds.GetRecord(strcmd);
            DataTableReader dr = Dat.CreateDataReader();
            if (dr.Read())
                userID = dr.GetInt32(0);
            else
                userID = 0;

            return userID;
        }

        /// <summary>
        /// 取得用户权限
        /// </summary>
        /// <param name="_User"></param>
        public static void GetUserRight(User _User)
        {
            string strcmd;
            strcmd = "select r.MenuID " +
                "from t_UserRight r,t_User u " +
                "where r.UserID=u.UserID " +
                "and u.UserCode ='" + _User.UserCode + "' " +
                "order by MenuID ";
            DataSet Dats = ds.GetRecord(strcmd);
            DataTableReader dr = Dats.CreateDataReader();
            _User.Right.Clear();
            while (dr.Read())
            {
                _User.Right.Add(dr[0].ToString());
            }
        }

        /// <summary>
        /// 设置用户权限
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="rightList"></param>
        /// <returns></returns>
        public static  Boolean SetUserRight(User _User)
        {
            string strCmd;
            int userID;
            Boolean Result = true;

            userID = UserGetIDByCode(_User.UserCode);

            strCmd = "delete t_UserRight where UserID=" + userID + "";
            ds.ExecuteSQL(strCmd);

            foreach (string strRight in _User.Right)
            {
                strCmd = "insert t_UserRight (UserID,MenuID,CreateBy,CreateDate) values(" +
                    userID.ToString() + "," + strRight + "," + App.AppUser.UserID.ToString() + ",GetDate())";
                if (!ds.ExecuteSQL(strCmd))
                {
                    messages.Clear();
                    messages.Add(ds.ErrorMessage);
                    Result = false;
                }
            }
            return Result;
        }
        #endregion

        #region 库存
        /// <summary>
        /// 将补货计划数据保存生成补货计划单
        /// </summary>
        /// <param name="dv">补货计划数据视图</param>
        public static string SOSave(DataView dv)
        {
            string result="";
            string SqlCmd;
            string SONextNumber;
            SONextNumber = SOGetNextNumber();
            SqlCmd = "insert t_SO (SONumber,SODate) values ('"+SONextNumber+"',GetDate())";
            foreach (DataRowView dr in dv)
            {
                SqlCmd = SqlCmd+" insert t_SOEntry (SONumber,ProdID,SOQty) values ('"+SONextNumber+"',"+dr["ProdID"].ToString()+","+dr["SupplyQty"].ToString()+")";
            }
            if (ds.ExecuteSQL(SqlCmd))
            {
                result = SONextNumber;
            }
            messages.Clear();
            messages.Add(ds.ErrorMessage);
            return result;
        }

        /// <summary>
        /// 取得下一补货计划号
        /// </summary>
        /// <returns></returns>
        public static string SOGetNextNumber()
        {
            int NumberMaxLength = 6;
            string result="";
            string Prefix = "SO";
            int NextNumber;
            string SqlCmd;
            SqlCmd = "select MAX(SONumber) from t_SO where SONumber like '"+Prefix+"%'";
            DataTableReader dr = ds.GetRecord(SqlCmd).CreateDataReader();
            dr.Read();
            if (dr[0]==DBNull.Value)
            {
                NextNumber = 1;
            }
            else
            {
                NextNumber=Convert.ToInt32(dr[0].ToString().Substring(Prefix.Length)) + 1;
            }
            result = Prefix + NextNumber.ToString().PadLeft(NumberMaxLength, '0');
            return result; 
        }
        /// <summary>
        /// 根据提单生成补货计划
        /// </summary>
        /// <param name="supplyDate">补货日期</param>
        /// <returns></returns>
        public static DataView SupplyStockByDo(DateTime doDate)
        {
            string SqlCmd;
            SqlCmd = "select a.OrderQty,P.ProdID,P.ProdName,P.ProdType,a.SupplyQty "+
                "from (select de.prodID, sum(de.OrderQty) as OrderQty,sum(OrderQty-ISNULL(s.Quantity,0)-isnull(so.SOQty,0)) as SupplyQty "+
                "from t_DOEntry de join t_DO d "+
                "on de.DeliveryNumber=d.DeliveryNumber "+
                "and de.OrderQty<" + MaxPickQty.ToString() +
                "left join t_Stock s "+
                "on de.ProdID=s.ProdID  "+
                "left join (select se.ProdID,sum(se.SOQty) as SOQty " +
                "from t_SOEntry se,t_SO s "+
                "where se.SONumber=s.SONumber "+
                "and DATEDIFF(day,s.SODate,'"+ doDate.ToString("yyyyMMdd") +"')=0 "+
               "group by se.ProdID) so "+
                "on de.ProdID=so.ProdID "+
                "where OrderQty-ISNULL(s.Quantity,0)-isnull(so.SOQty,0)>0 "+
                "and DATEDIFF(day,d.OrderDatetime,'"+ doDate.ToString("yyyyMMdd") +"')=0 "+
               "group by de.ProdID  ) a,t_Product P "+
                "where a.ProdID=P.ProdID";
            return ds.GetRecord(SqlCmd).Tables[0].DefaultView;
        }

        /// <summary>
        /// 根据预测生成补货计划
        /// </summary>
        /// <param name="supplyDate">补货日期</param>
        /// <returns></returns>
        public static DataView SupplyStockByForecast(DateTime supplyDate)
        {
            string SqlCmd;
            SqlCmd = "select a.OrderQty ,p.ProdID,p.ProdName,p.ProdType,a.SupplyQty"+
                " from t_product p,"+
                "(select TOP 10 de.ProdID, sum(de.OrderQty) as OrderQty,SUM(de.SentQty) as SupplyQty "+
                "from t_DO d,t_DOEntry de "+
                "where d.DeliveryNumber=de.DeliveryNumber "+
                "and de.OrderQty<" + MaxPickQty.ToString() +
                " and datediff(day,d.OutStockTime,'" + supplyDate.ToString("yyyyMMdd") + "')=7" +
                "group by de.ProdID "+
                "order by SUM(de.SentQty) desc) a " +
                "where p.ProdID=a.ProdID";
            return ds.GetRecord(SqlCmd).Tables[0].DefaultView;
        }

        /// <summary>
        /// 库存导入
        /// </summary>
        /// <param name="dv">导入的数据源</param>
        public static void StockImport(DataView dv)
        {
            int prodID;
            string sqlCmd;
            messages.Clear();
            
            sqlCmd = string.Format("delete t_Stock");
            Ds.ExecuteSQL(sqlCmd);
            foreach (DataRowView dr in dv)
            {
                prodID=GetProdIDByCode(dr[0].ToString());
                if (prodID != 0)
                {
                    StockUpdate(1, prodID, Convert.ToDouble(dr[1]));  //修改库存，默认库位统一为1－拣货区
                }
                else
                {
                    messages.Add(dr[0].ToString());
                }
            }
        }

        /// <summary>
        /// 修改库存
        /// </summary>
        /// <param name="_prodID">产品ID</param>
        /// <param name="_qty">产品库存数量</param>
        public static void StockUpdate(int _areaID,int _prodID, double _qty)
        {
            string sqlCmd;
            sqlCmd=string.Format("if exists(select * from t_Stock where AreaID={0} and ProdID={1}) "+
                "update t_Stock set Quantity={2} where AreaID={0} and ProdID={1} "+
                "else insert t_Stock (AreaID,ProdID,Quantity) values ({0},{1},{2})",_areaID,_prodID,_qty);
            Ds.ExecuteSQL(sqlCmd);
        }
        #endregion

        #region 数据库连接
        /// <summary>
        /// 取得数据库连接
        /// 当前程序是从ini文件取（可以在此改为其他方式取）
        /// </summary>
        /// <returns></returns>
        public static DBConnection GetDBConnection()
        {
            DBConnection Result = new DBConnection();

            Encryption encrypt = new Encryption();
            string iniFileName = "";
            const int encryptKey = 1234;
            IniSetting MySettings;
            iniFileName = stAppPath + "\\" + stIniFileName; 
            MySettings = new IniSetting(iniFileName);

            Result.DBServer = MySettings.GetValue("DBConnection", "DBServer");
            Result.DBName = MySettings.GetValue("DBConnection", "DBName");
            Result.UserName = MySettings.GetValue("DBConnection", "UserName");
            Result.Password = encrypt.Decrypt(MySettings.GetValue("DBConnection", "Password"), encryptKey);
            return Result;
        }
        /// <summary>
        /// 取得数据库连接
        /// 当前程序是从ini文件取（可以在此改为其他方式取）
        /// </summary>
        /// <returns></returns>
        public static string GetSqlConnection()
        {
            

            Encryption encrypt = new Encryption();
            string iniFileName = "";
            const int encryptKey = 1234;
            IniSetting MySettings;
            iniFileName = stAppPath + "\\" + stIniFileName;
            MySettings = new IniSetting(iniFileName);
            string sssqlconnection = "server=" + MySettings.GetValue("DBConnection", "DBServer")
                + ";database=" + MySettings.GetValue("DBConnection", "DBName")
                + ";uid=" + MySettings.GetValue("DBConnection", "UserName")
                + ";pwd=" + encrypt.Decrypt(MySettings.GetValue("DBConnection", "Password"), encryptKey) + ";";
            
            return sssqlconnection;
        }

       
        public static void SaveDBConnection(DBConnection _con)
        {
            Encryption encrypt = new Encryption();
            string iniFileName = "";
            const int encryptKey = 1234;
            IniSetting MySettings;
            iniFileName = stAppPath + "\\" + stIniFileName; 
            MySettings = new IniSetting(iniFileName);
            MySettings.SetValue("DBConnection", "DBServer", _con.DBServer);
            MySettings.SetValue("DBConnection", "DBName", _con.DBName);
            MySettings.SetValue("DBConnection", "UserName", _con.UserName);
            MySettings.SetValue("DBConnection", "Password", encrypt.Encrypt(_con.Password, encryptKey));
        }

        public static DBSource ConnectDB(DBConnection _dc)
        {
            DBSource LoginDS;
            LoginDS = new SqlsvrDBSource(_dc);

            if (!LoginDS.Connect())
            {
                messages.Clear();
                messages.Add("数据库服务器连接不正确，请重新设置！错误信息：" + LoginDS.ErrorMessage);
                return null;
            }
            return LoginDS;
        }
        #endregion    
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {

            using (SqlConnection connection = new SqlConnection(sqlconnection))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }

        }

        #region 产品

        /// <summary>
        /// 根据SQL语句取得产品（程序内部调用，用户不可见）
        /// </summary>
        /// <param name="SqlCmd">Sql语句</param>
        /// <returns>产品，未找到返回null</returns>
        private static Product ProductGet(string SqlCmd)
        {
            Product result = null;
            DataTableReader dr = ds.GetRecord(SqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = new Product();
                result.ProdID = Convert.ToInt32(dr["ProdID"]);
                result.ProdCode = dr["ProdCode"].ToString();
                result.ProdName = dr["ProdName"].ToString();
                result.ProdType = dr["ProdType"].ToString();
                result.Unit = dr["Unit"].ToString();
                result.QtyOnBoard = Convert.ToInt32(dr["QtyOnBoard"]);
                result.ProdTypeID = Convert.ToInt32(dr["ProdTypeID"]);
                result.Volume = Convert.ToDouble(dr["Volume"]);
                result.Weight = Convert.ToDouble(dr["Weight"]);
                result.UnitQty = Convert.ToInt32(dr["UnitQty"]);
            }
            return result;
        }

        /// <summary>
        /// 根据产品編号取得该产品
        /// </summary>
        /// <param name="_doNumber">产品編号</param>
        /// <returns>产品</returns>
        public static Product ProductGetByCode(string _prodCode)
        {
            string SqlCmd;
            SqlCmd = "Select * from t_Product where ProdCode='" + _prodCode.Trim() + "'";
            return ProductGet(SqlCmd);
        }

        /// <summary>
        /// 根据产品代码获取产品ID
        /// </summary>
        /// <param name="_prodCode">产品代码</param>
        /// <returns></returns>
        public static int GetProdIDByCode(string _prodCode,SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select ProdID from t_Product where ProdCode='"+_prodCode.Trim()+"'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 根据产品类型获取产品类型ID
        /// </summary>
        /// <param name="_prodCode">产品代码</param>
        /// <returns></returns>
        public static int GetProdtypeByname(string ProdTypeName, SqlConnection connection, SqlCommand cmd) 
        {
            string sqlCmd;
            sqlCmd = "SELECT ProdTypeID from t_ProductType where ProdTypeName='" + ProdTypeName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据产品类型获取产品类型ID
        /// </summary>
        /// <param name="_prodCode">产品代码</param>
        /// <returns></returns>
        public static int GetProdtypeByproduct(string Prodid, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select ProdTypeID from t_Product where  ProdID ='" + Prodid.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据产品品质获取产品品质ID
        /// </summary>
        /// <param name="_prodCode">产品代码</param>
        /// <returns></returns>
        public static int GetStockPinZhiIDByname(string ProdTypeName, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = " SELECT StockPinZhiID from t_StockPinZhi where StockPinZhiMiaoShu='" + ProdTypeName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        //获取产品信息
        public static DataTable GetProductInfo(string sqlcase)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select prodcode as 产品代码,prodname as 产品名称,prodtype as 产品规格,unit as 单位,qtyonboard as 每板数量,anquantianshu 安全库存天数, zuidatianshu 最大库存天数" +
                          " from t_Product " +
                          " where 1=1 " + sqlcase;
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取产品类别
        public static DataTable GetProductType()
        {
            DataSet dt = new DataSet();
            string sqlstr = "select * from t_producttype order by prodtypeid";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //保存产品信息
        public static bool SaveProduct(Product pd)
        {
            pd.ProdID = GetMaxProdID();
            string anquan = "null";
            if(pd.Anquantianshu>0)
            {
                anquan = Convert.ToString(pd.Anquantianshu);
            }
            string zuida = "null";
            if (pd.Zuidatianshu > 0)
            {
                zuida = Convert.ToString(pd.Zuidatianshu);
            }
           
            string sqlstr = "insert t_product (prodid,supplierid,prodcode,prodname,prodtype,unit,qtyonboard,prodtypeid,volume,weight,unitqty,anquantianshu,zuidatianshu) values ('" +
                             pd.ProdID + "','" + pd.SupplierID + "','" + pd.ProdCode + "','" + pd.ProdName + "','" + pd.ProdType + "','" + pd.Unit + "','" + pd.QtyOnBoard + "','" + pd.ProdTypeID + "','" + pd.Volume + "','" + pd.Weight + "','" + pd.UnitQty + "'," + anquan + "," + zuida + ")";
            return ds.ExecuteSQL(sqlstr);
        }
        //更新产品信息
        public static bool UpdateProduct(Product pd)
        {
            string anquan = "null";
            if (pd.Anquantianshu > 0)
            {
                anquan = Convert.ToString(pd.Anquantianshu);
            }
            string zuida = "null";
            if (pd.Zuidatianshu > 0)
            {
                zuida = Convert.ToString(pd.Zuidatianshu);
            }
            string sqlstr = " update t_product set prodname='" + pd.ProdName + "',prodtype='" + pd.ProdType + "',unit='" + pd.Unit + "',qtyonboard='" + pd.QtyOnBoard + "',prodtypeid='" + pd.ProdTypeID + "',volume='" + pd.Volume + "',weight='" + pd.Weight + "',unitqty='" + pd.UnitQty + "',anquantianshu=" + anquan + ",zuidatianshu=" + zuida +
                          " where prodcode='" + pd.ProdCode + "'";
            return ds.ExecuteSQL(sqlstr);
        }
        //获取最大产品号
        public static int GetMaxProdID()
        {
            int maxid;
            string sqlstr = " select Max(prodid) from t_product";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (dr[0] == DBNull.Value)
            {
                maxid = 1;
            }
            else
            {
                maxid = Int32.Parse(dr[0].ToString()) + 1;
            }
            return maxid;
        }
        //检查产品代码
        public static int checkProdcode(string prodcode)
        {
            DataSet dt = new DataSet();
            int rowcount;
            string sqlstr = "select * from t_product where prodcode='" + prodcode + "'";
            dt = ds.GetRecord(sqlstr);
            rowcount = dt.Tables[0].Rows.Count;
            return rowcount;
        }

        //获取产品信息
        public static DataTable GetProductByCode(string prodcode)
        {
            DataSet dt = new DataSet();
            string sqlstr = "select p.*,pt.prodtypename from t_product p,t_producttype pt where p.prodtypeid=pt.prodtypeid and prodcode='" + prodcode + "'";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
        //获取产品所属供应商信息
        public static DataTable GetSupplier()
        {
            DataSet dt = new DataSet();
            string sqlstr = " select supplierid,shortname from t_supplier";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }
         
        #endregion

        #region 提单
        /// <summary>
        /// 提单打印次数+1
        /// </summary>
        /// <param name="_doNumber">提单号码</param>
        /// <returns>成功返回true，失败返回false</returns>
        public static bool DOAddPrintTimes(string _doNumber)
        {
            bool result=true;
            string SqlCmd;
            SqlCmd = "update t_DO set PrintTimes=PrintTimes+1,CanBePrinted=0 where DeliveryNumber='" + _doNumber.Trim() + "'";
            ds.ExecuteSQL(SqlCmd);
            return result;
        }

        /// <summary>
        /// 判断提单是否可以打印
        /// </summary>
        /// <param name="_doNumber">提单号</param>
        /// <returns>可以打印返回True，否则返回False</returns>
        public static bool DOCanBePrinted(string _doNumber)
        {
            bool result = true;
            string SqlCmd;
            SqlCmd = "select CanBePrinted from t_DO where DeliveryNumber='" + _doNumber.Trim() + "'";
            DataTableReader dr = ds.GetRecord(SqlCmd).CreateDataReader();
            dr.Read();
            result=Convert.ToBoolean(dr["CanBePrinted"]);
            return result;
        }

        /// <summary>
        /// 提单打印重置
        /// </summary>
        /// <param name="_doNumber">提单号码</param>
        /// <returns>成功返回true，失败返回false</returns>
        public static bool DOResetPrint(string _doNumber)
        {
            bool result = true;
            string SqlCmd;
            SqlCmd = "update t_DO set CanBePrinted=1 where DeliveryNumber='" + _doNumber.Trim() + "'";
            ds.ExecuteSQL(SqlCmd);
            return result;
        }

        public static bool DOSave(DeliveryOrder _do)
        {
            bool result;
            result= ds.ExecuteSQL(_do.GetSqlSave());
            return result;
        }

        /// <summary>
        /// 根据SQL语句取得提单（程序内部调用，用户不可见）
        /// </summary>
        /// <param name="SqlCmd">Sql语句</param>
        /// <returns>发票，未找到返回null</returns>
        private static DeliveryOrder DOGet(string SqlCmd)
        {
            DeliveryOrder result = null;
            DataTableReader dr = ds.GetRecord(SqlCmd).CreateDataReader();
            if (dr.Read())
            {
                //读取提单头
                result = new DeliveryOrder();
                result.DeliveryNumber = dr["DeliveryNumber"].ToString();
                result.OrderNumber = dr["OrderNumber"].ToString();
                result.OrderType = dr["OrderType"].ToString();
                result.SaleType = dr["SaleType"].ToString();
                result.PONumber = dr["PONumber"].ToString();
                result.OrderDatetime = Convert.ToDateTime(dr["OrderDatetime"]);
                result.SoldTo = dr["SoldTo"].ToString();
                result.DeliveryTo = dr["DeliveryTo"].ToString();
                result.CustName = dr["CustName"].ToString();
                result.DeliveryAddress = dr["DeliveryAddress"].ToString();
                result.CarrierID = Convert.ToInt32(dr["CarrierID"]);
                result.VehicleNumber = dr["VehicleNumber"].ToString();
                result.Remark = dr["Remark"].ToString();
                result.StatusID = Convert.ToInt32(dr["StatusID"]);
                if (dr["InStockTime"] != DBNull.Value)
                {
                    result.InStockTime = Convert.ToDateTime(dr["InStockTime"]);
                }
                if (dr["OutStockTime"] != DBNull.Value)
                {
                    result.OutStockTime = Convert.ToDateTime(dr["OutStockTime"]);
                }

                //读取提单明细
                DOEntry _doEntry;
                SqlCmd = "Select * from t_DOEntry where DeliveryNumber='" + result.DeliveryNumber.Trim() + "' Order by ProdID";
                dr = ds.GetRecord(SqlCmd).CreateDataReader();
                while (dr.Read())
                {
                    _doEntry = new DOEntry();
                    _doEntry.ProdID = Convert.ToInt32(dr["ProdID"]);
                    if (dr["OrderQty"] == DBNull.Value)
                    {
                        _doEntry.OrderQty = 0;
                    }
                    else
                    {
                        _doEntry.OrderQty = Convert.ToDouble(dr["OrderQty"]);
                    }
                    if (dr["SentQty"] == DBNull.Value)
                    {
                        _doEntry.SentQty = 0;
                    }
                    else
                    {
                        _doEntry.SentQty = Convert.ToDouble(dr["SentQty"]);
                    }
                    if (dr["UnitWeight"] == DBNull.Value)
                    {
                        _doEntry.UnitWeight = 0;
                    }
                    else
                    {
                        _doEntry.UnitWeight = Convert.ToDouble(dr["UnitWeight"]);
                    }
                    if (dr["Weight"] == DBNull.Value)
                    {
                        _doEntry.Weight = 0;
                    }
                    else
                    {
                        _doEntry.Weight = Convert.ToDouble(dr["Weight"]);
                    }
                    result.Items.Add(_doEntry);
                }
            }
            return result;
        }

        /// <summary>
        /// 根据提单号取得该提单
        /// </summary>
        /// <param name="_doNumber">提单号</param>
        /// <returns>提单</returns>
        public static DeliveryOrder DOGetByNumber(string _doNumber)
        {
            string SqlCmd;
            SqlCmd = "Select * from t_DO where DeliveryNumber='" + _doNumber.Trim() + "'";
            return DOGet(SqlCmd);
        }

        /// <summary>
        /// 根据供货商取得未打印提单列表
        /// </summary>
        /// <param name="_supplierID">供货商ID</param>
        /// <returns></returns>
        public static DataView DOGetUnPrintedBySupplier(int _supplierID)
        {
            string SqlCmd;
            SqlCmd = "select * from t_DO where CanbePrinted=1 "
                +"and SupplierID="+_supplierID.ToString();
            return ds.GetRecord(SqlCmd).Tables[0].DefaultView;
        }
        #endregion

        #region 捡货单
        /// <summary>
        /// 通过车号判断是否已经完成集货
        /// </summary>
        /// <param name="vehiclenumber">车号</param>
        /// <returns>返回result</returns>
        public static bool IsProdSetted(string vehiclenumber)
        {
            string SqlCmd;
            bool result;
            SqlCmd = "select distinct m.* from  t_MO m,t_moentry me,t_DO d " +
                    "where m.MergeNumber=me.MergeNumber " +
                    "and me.DONumber=d.DeliveryNumber " +
                    "and d.VehicleNumber ='"+ vehiclenumber +"' " +
                    "and m.IsFinished ='False' " +
                    "and m.IsProdSetted ='True'";
            DataSet dt = ds.GetRecord(SqlCmd);
            if (dt.Tables[0].Rows.Count > 0)
            {
                result = true;
            }
            else
            {
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 通过车号获取未完成的捡货单号
        /// </summary>
        /// <param name="vehiclenumber">车号</param>
        /// <returns>捡货单号</returns>
        public static DataTable GetMoNumByVehNum(string vehiclenumber)
        {
            DataSet dt = new DataSet();
            string SqlCmd;
            SqlCmd ="select distinct m.MergeNumber from  t_MO m,t_moentry me,t_DO d " +
                    "where m.MergeNumber=me.MergeNumber " +
                    "and me.DONumber=d.DeliveryNumber " +
                    "and d.VehicleNumber ='"+ vehiclenumber +"' " +
                    "and m.IsFinished ='False' " +
                    "and m.IsProdSetted ='True'";
            dt = ds.GetRecord(SqlCmd);
            return dt.Tables[0];
        }

        /// <summary>
        /// 更新捡货单车位
        /// </summary>
        /// <param name="mergenumber">捡货单号</param>
        /// <returns></returns>
        public static bool UpdateMergeParking(string mergenumber,string parkingname)
        {
            bool result;
            string sqlCmd = "update t_mo set parkingname='" + parkingname + "' where mergenumber='" + mergenumber + "'";
            result = ds.ExecuteSQL(sqlCmd);
            return result;
        }

        /// <summary>
        /// 更新捡货单状态
        /// </summary>
        /// <param name="vehilclenumber">捡货单号</param>
        /// <returns></returns>
        public static bool UpdateMergeStatus(string vehiclenumber)
        {
            bool result;
            string sqlCmd = "update t_mo set isfinished=1 where mergenumber=(select distinct m.MergeNumber from  t_MO m,t_moentry me,t_DO d "+
                           " where m.MergeNumber=me.MergeNumber"+
                           " and me.DONumber=d.DeliveryNumber"+
                           " and d.VehicleNumber ='"+vehiclenumber +"'"+
                           " and m.IsFinished =0"+
                          " and m.IsProdSetted =1"+
                          " and m.ParkingName is not null)";
            result = ds.ExecuteSQL(sqlCmd);
            return result;
        }


        #endregion

        #region 读卡器
        /// <summary>
        /// 取得登记过的读卡器列表(按设备编号排序)
        /// </summary>
        /// <returns></returns>
        public static DataView CardReaderGetList()
        {
            DataView dv = new DataView();
            string strCmd;
            strCmd = "select c.DeviceSN,c.DeviceNumber,c.SiteID,c.ComPort,BaudRate,s.SiteName " +
                "from t_CardReader c,t_Site s " +
                "where c.SiteID=s.SiteID " +
                " order by c.DeviceNumber";

            dv = Ds.GetRecord(strCmd).Tables[0].DefaultView;
            return dv;
        }

        /// <summary>
        /// 增加读卡器记录
        /// </summary>
        /// <param name="CR">读卡器</param>
        /// <param name="SiteID">读卡点</param>
        /// <returns></returns>
        public static Boolean CardReaderAdd(CardReader CR, int SiteID)
        {
            string strCmd;
            int SerialNumber;
            ICItems SN = new ICItems(8);
            SerialNumber = Convert.ToInt32(CR.DeviceNumber);
            SN[7] = (byte)SerialNumber;

            IniSetting MySettings;
            string SettingsName = stAppPath + "\\" + stIniFileName;
            MySettings = new IniSetting(SettingsName);

            CR.SetDeviceSerialNumber(SN);
            CR.GetDeviceSerialNumber();

            strCmd = "insert t_CardReader (DeviceSN,DeviceNumber,SiteID,ComPort,BaudRate) values('" 
                + CR.SerialNumber.ToString() + "','" + CR.DeviceNumber + "'," + SiteID.ToString() + "," + CR.ComPort.ToString() +
                "," + CR.BaudRate.ToString() + ")";

            if (Ds.ExecuteSQL(strCmd))
            {
                MySettings.SetValue("CardReader", "ComPort", CR.ComPort.ToString());
                MySettings.SetValue("CardReader", "BaudRate", CR.BaudRate.ToString());
                return true;
            }
            else
            {
                messages.Clear();
                messages.Add(ds.ErrorMessage);
                return false;
            }
        }
        #endregion

        #region 读卡点
        /// <summary>
        /// 取得读卡点列表清单
        /// </summary>
        /// <returns></returns>
        public static DataView SiteGetList()
        {
            DataSet Datas = new DataSet();
            string strCmd;
            strCmd = "select * from t_Site order by SiteCode";
            Datas = Ds.GetRecord(strCmd);
            return Datas.Tables[0].DefaultView;
        }

      
        #endregion

        #region IC卡
        /// <summary>
        /// 添加注册的IC卡
        /// </summary>
        /// <param name="_card">IC卡</param>
        /// <returns></returns>
        public static Boolean ICCardAdd(ICCard _card)
        {
            messages.Clear();
            string strCmd;
            strCmd = "insert t_ICCard (CardID,CardNumber,CardSN,Remark) values (" +
                _card.CardID.ToString() + ",'" + _card.CardNumber + "','" + _card.CardSN + "','" + _card.Remark + "')";

            if (Ds.ExecuteSQL(strCmd))
            {
                return true;
            }
            else
            {
                messages.Add(Ds.ErrorMessage);
                return false;
            }
        }

        /// <summary>
        /// 更改IC卡状态（注销）
        /// </summary>
        /// <param name="_cardID">IC卡ID</param>
        /// <returns></returns>
        public static Boolean ICCardModifyStatus(int _cardID, int _statusID)
        {
            string strCmd;
            strCmd = "update t_ICCard set Status=" + _statusID.ToString() + " where CardID=" + _cardID.ToString();

            if (Ds.ExecuteSQL(strCmd))
            {
                messages.Clear();
                messages.Add("IC卡状态修改成功！");
                return true;
            }
            else
            {
                messages.Clear();
                messages.Add(Ds.ErrorMessage);
                return false;
            }
        }

        /// <summary>
        /// 取得数据库中最大的CardID号码
        /// </summary>
        /// <returns></returns>
        public static int ICCardGetMaxID()
        {
            int result = 0;
            string sqlCmd;

            sqlCmd = "select MAX(CardID) from t_ICCard";
            DataTableReader dtrCard = Ds.GetRecord(sqlCmd).CreateDataReader();
            dtrCard.Read();
            if (dtrCard[0].ToString() == string.Empty)
            {
                result = 0;
            }
            else
            {
                result = int.Parse(dtrCard[0].ToString());
            }
            return result;
        }

        /// <summary>
        /// 取得IC卡列表
        /// </summary>
        /// <returns></returns>
        public static DataView ICCardGetList()
        {
            DataSet DataS = new DataSet();
            string strCmd;
            strCmd = "select c.CardID,c.CardNumber,c.CardSN,c.Remark " +
                "from t_ICCard c" +
                " order by CardNumber";
            DataS = Ds.GetRecord(strCmd);
            return DataS.Tables[0].DefaultView;
        }

        /// <summary>
        /// 发卡信息保存数据到数据库
        /// </summary>
        /// <param name="_issuedCard"></param>
        /// <returns></returns>
        public static Boolean SaveIssuedCard(IssuedCard _issuedCard)
        {
            Boolean result;
            string sqlCmd;
            result = false;
            sqlCmd = "insert t_IssuedCard (IssueNumber,CardID,FlowID,VehicleNumber,Driver,MobileNumber,IDNumber,Remark,TimeStamp,SiteID,UserID,Company,PersonsOn) values ('" +
                _issuedCard.IssuedNumber + "','" + _issuedCard.CardID.ToString() + "','" + _issuedCard.FlowID.ToString() + "','" + _issuedCard.VehicleNumber + "','" +
                _issuedCard.Driver + "','" + _issuedCard.MobileNumber + "','" + _issuedCard.IDNumber + "','" +
                _issuedCard.Remark + "',GetDate(),'" + _issuedCard.SiteID.ToString() +
                "','" + _issuedCard.UserID.ToString() + "','" + _issuedCard.Company.Trim() + "','"+ _issuedCard.PersonsOn.ToString() + "')";
            if (ds.ExecuteSQL(sqlCmd))
            {
                result = true;
            }
            else
            {
               
                result = false;
            }

            return result;
        }

        /// <summary>
        /// 数据保存到数据库
        /// </summary>
        /// <param name="_rr"></param>
        public static bool SaveReadRecord(ReadRecord _rr)
        {
            bool result;
            int intIsFastWay;
            if (_rr.IsFastWay)
            {
                intIsFastWay = 1;
            }
            else
            {
                intIsFastWay = 0;
            }
            string sqlCmd;
            sqlCmd = "insert t_ReadRecord (IssueNumber,SiteID,TimeStamp,IsFastWay,UserID,Remark,Status,OldValue,NewValue) values ('" +
                _rr.IssueNumber.Trim() + "'," + _rr.SiteID.ToString() + ",getdate()," +
                intIsFastWay.ToString() + "," + _rr.UserID.ToString() + ",'" + _rr.Remark.Trim() + "'," + _rr.Status.ToString() +
                ",'" + _rr.OldValue.Trim() + "','" + _rr.NewValue.Trim() + "')";
            if (ds.ExecuteSQL(sqlCmd))
            {
                result = true;
            }
            else
            {
                
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 检查卡是否已在用
        /// </summary>
        /// <param name="_CardID"></param>
        /// <returns></returns>
        public static Boolean IsInUsed(int _CardID)
        {
            Boolean result;
            string sqlCmd;
            sqlCmd = "select COUNT(*) from t_IssuedCard where CardID=" + _CardID.ToString() + " and IssueStatus=0";
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            dr.Read();
            result = (Convert.ToInt32(dr[0]) > 0);
            return result;
        }

        ///// <summary>
        ///// 判断卡是否设置过此流程
        ///// </summary>
        ///// <param name="_cardID"></param>
        ///// <param name="_flowID"></param>
        ///// <returns></returns>
        //public static Boolean FlowIsValid(int _cardID, int _flowID)
        //{
        //    Boolean result = false;
        //    string sqlCmd;

        //    sqlCmd = "select f.SiteID" +
        //        " from t_ICCard c,t_Flow f" +
        //        "  where c.CardID =" + _cardID.ToString() +
        //        " and f.FlowID=" + _flowID.ToString();
        //    DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
        //    if (dr.HasRows)
        //    {
        //        result = true;
        //    }
        //    return result;
        //}

        /// <summary>
        /// 取得下一单号
        /// </summary>
        /// <returns></returns>
        public static string GetNextNumber()
        {
            string result = "";
           
           
            string sqlCmd;
            string flag = "IC";
            string temp;
           

            sqlCmd = "select max(IssueNumber) from t_issuedCard ";
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            dr.Read();
            temp = dr[0].ToString();
            if (string.IsNullOrEmpty(temp))
            {
                
                result =flag + "00000001";
            }
            else
            {
               
                result =flag +(Convert .ToInt32 (temp.Substring (2,8))+1).ToString ("00000000");
            }

            return result;
        }

        /// <summary>
        /// 通过读卡器序列号取得当前的节点位置
        /// </summary>
        /// <param name="_CardReaderSN"></param>
        /// <returns></returns>
        public static Site GetSiteByCardReaderSN(string _CardReaderSN)
        {
            Site result = new Site();
            string sqlCmd;
            sqlCmd = "select s.SiteID,s.SiteCode,s.SiteName,s.PassTimes,s.SiteDescription " +
                " from t_CardReader r,t_Site s" +
                " where r.SiteID=s.SiteID" +
                " and r.DeviceSN='" + _CardReaderSN.ToString().Trim() + "'";
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result.SiteID = Convert.ToInt32(dr["SiteID"]);
                result.SiteCode = dr["SiteCode"].ToString();
                result.SiteName = dr["siteName"].ToString();
                result.SiteDescription = dr["SiteDescription"].ToString();
                result.Passtimes = Convert.ToInt32(dr["Passtimes"]);
            }
            return result;
        }

        /// <summary>
        /// 通过序列号获得内部编码ID
        /// </summary>
        /// <param name="serialNumber">卡序列号</param>
        /// <returns></returns>
        public static int GetCardIDBySN(string serialNumber)
        {
            int result = 0;
            string sqlCmd;

            sqlCmd = "select CardID from t_ICCard where CardSN='" + serialNumber + "'";
            DataTableReader dtrCard = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dtrCard.Read())
            {
                result = Convert.ToInt32(dtrCard[0]);
            }
            return result;
        }

        /// <summary>
        /// 根据IC卡ID号取得IssueNumber
        /// </summary>
        /// <param name="_cardID"></param>
        /// <returns></returns>
        public static string GetIssueNumberByCardID(int _cardID)
        {
            string result;
            string sqlCmd;
            sqlCmd = "select IssueNumber from t_IssuedCard where IssueStatus !=99 and CardID=" + _cardID.ToString();  //状态!＝99的都是回收掉的卡
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.HasRows)
            {
                dr.Read();
                result = dr[0].ToString();
            }
            else
            {
                result = "";
            }
            return result;
        }

        /// <summary>
        /// 根据IssueNumber取得此单内容
        /// </summary>
        /// <param name="_issueNumber">单号</param>
        /// <returns></returns>
        public static IssuedCard GetIssuedCardByNumber(string _issueNumber)
        {
            IssuedCard result = new IssuedCard();
            string sqlCmd;
            sqlCmd = "select * from t_IssuedCard where  IssueNumber='" + _issueNumber.Trim() + "'";
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.HasRows)
            {
                dr.Read();
                result.IssuedNumber = dr["IssueNumber"].ToString();
                result.CardID = Convert.ToInt32(dr["CardID"]);
                result.FlowID = Convert.ToInt32(dr["FlowID"]);
                result.VehicleNumber = dr["VehicleNumber"].ToString();
                result.DONumber = dr["DONumber"].ToString();
                result.Driver = dr["Driver"].ToString();
                result.MobileNumber = dr["MobileNumber"].ToString();
                result.IDNumber = dr["IDNumber"].ToString();
                result.Remark = dr["Remark"].ToString();
                result.QueueNumber = dr["QueueNumber"].ToString();
                result.TimeStamp = Convert.ToDateTime(dr["TimeStamp"]);
                result.IssueStatus = Convert.ToInt32(dr["IssueStatus"]);
                result.SiteID = Convert.ToInt32(dr["siteID"]);
                result.Company = dr["Company"].ToString().Trim();
                result.ToVisit = dr["ToVisit"].ToString().Trim();
                result.IsFirstTimeLimited = Convert.ToBoolean(dr["IsFirstTimeLimited"]);
                result.PersonsOn = Convert.ToInt32(dr["PersonsOn"]);
            }
            return result;
        }

        /// <summary>
        /// 取得经过的节点列表
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <returns></returns>
        public static DataView GetPassedNodeList(string _issueNumber)
        {
            DataView dv = new DataView();
            string strCmd;
            strCmd = "select s.SiteName,r.TimeStamp,r.Remark,st.StatusName" +
                 " from t_ReadRecord r,t_Site s,t_Status st" +
                 " where r.SiteID=s.SiteID" +
                 " and r.Status=st.StatusID" +
                 " and st.StatusType ='[Record]'" +
                " and IssueNumber='" + _issueNumber.Trim() + "'" +
                " order by TimeStamp";

            dv = ds.GetRecord(strCmd).Tables[0].DefaultView;
            return dv;
        }

        /// <summary>
        /// 回收IC卡
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <param name="_siteID"></param>
        /// <returns></returns>
        public static bool CallbackCard(string _issueNumber, int _siteID, int _userID, string _remark)
        {
            bool result = false;
            ReadRecord _readRecord = new ReadRecord();
            
            string sqlCmd;

            //登记刷卡记录
            _readRecord.SiteID = _siteID;
            _readRecord.UserID = _userID;
            _readRecord.IssueNumber = _issueNumber;
            _readRecord.IsFastWay = false;
            _readRecord.Status = 99;
            _readRecord.Remark = _remark;

            //判断回收卡时间限制


            if (SaveReadRecord(_readRecord))
            {
                sqlCmd = "update t_IssuedCard set IssueStatus=99,EndSiteID=" + _siteID.ToString() + ",EndUserID=" + _userID.ToString() +
                    ",EndTimeStamp=getdate()" +
                    " where IssueNumber='" + _issueNumber.Trim() + "'";
                if (ds.ExecuteSQL(sqlCmd))
                {
                    result = true;
                }
                else
                {
                    result = false;
                }
            }
            return result;
        }
        /// <summary>
        /// 异常回收IC卡
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <param name="_siteID"></param>
        /// <returns></returns>
        public static bool AbnormalCallbackCard(string _issueNumber, int _siteID, int _userID, string _remark)
        {
            bool result = false;
            ReadRecord _readRecord = new ReadRecord();
           
            string sqlCmd;

            //登记刷卡记录
            _readRecord.SiteID = _siteID;
            _readRecord.UserID = _userID;
            _readRecord.IssueNumber = _issueNumber;
            _readRecord.IsFastWay = false;
            _readRecord.Status = 98;
            _readRecord.Remark = _remark;
            if (SaveReadRecord (_readRecord))
            {
                sqlCmd = "update t_IssuedCard set IssueStatus=99,EndSiteID=" + _siteID.ToString() + ",EndUserID=" + _userID.ToString() +
                    ",EndTimeStamp=getdate()" +
                    " where IssueNumber='" + _issueNumber.Trim() + "'";
                if (ds.ExecuteSQL(sqlCmd))
                {
                    result = true;
                }
                else
                {
                    result = false;
                }
            }
            return result;
        }

        /// <summary>
        /// 取得已发卡列表
        /// </summary>
        /// <param name="_typeID">类型(<=0 代表全部类型)</param>
        /// <returns></returns>
        public static DataView GetIssuedCardList()
        {
            DataSet DataS = new DataSet();
            string strCmd;
            strCmd = "select c.CardNumber,f.FlowName,i.VehicleNumber,i.QueueNumber,i.TimeStamp,i.IssueNumber,s.SiteName,u.UserName,st.StatusName,i.FlowID,i.mobilenumber" +
                 " from t_IssuedCard i,t_ICCard c,t_FlowType f,t_Site s,t_user u,t_Status st" +
                 " where i.CardID=c.CardID" +
                 " and i.IssueStatus=st.StatusID" +
                 " and st.StatusType='[Issue]'" +
                 " and i.FlowID=f.FlowID" +
                 " and i.SiteID=s.SiteID" +
                 " and i.UserID=u.UserID" +
                 " and i.IssueStatus !=99";
                
          
            strCmd = strCmd + " Order by i.TimeStamp";
            DataS = ds.GetRecord(strCmd);
            return DataS.Tables[0].DefaultView;
        }

        /// <summary>
        /// 修改单据状态
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <param name="_status"></param>
        /// <returns></returns>
        public static bool ChangeIssuedCardStatus(string _issueNumber, int _status)
        {
            bool result;
            string sqlCmd;
            sqlCmd = "update t_IssuedCard set IssueStatus=" + _status.ToString() + " where IssueNumber='" + _issueNumber.Trim() + "'";
            if (ds.ExecuteSQL(sqlCmd))
            {
                result = true;
            }
            else
            {
                
                result = false;
            }
            return result;
        }

        #endregion

        #region 流程
        /// <summary>
        /// 取得流程类别清单列表
        /// </summary>
        /// <returns></returns>
        public static DataView GetFlowTypeList()
        {
            string sqlCmd;
            sqlCmd = "select * from t_flowType order by flowCode";
            return ds.GetRecord(sqlCmd).Tables[0].DefaultView;
        }

        /// <summary>
        /// 判断是否在黑名单中
        /// </summary>
        /// <param name="_typeID">黑名单类型</param>
        /// <param name="IDNumber">编号</param>
        /// <returns></returns>
        public static BlackList IsInBlackList(int _typeID, string IDNumber)
        {
            BlackList result = null;
            string strcmd;

            strcmd = "select * from t_blacklist where typeid=" + _typeID.ToString() + " and IDNumber='" + IDNumber.Trim() + "'";
            DataTableReader dr = ds.GetRecord(strcmd).CreateDataReader();
            if (dr.Read())
            {
                result = new BlackList();
                result.TypeID = Convert.ToInt16(dr["TypeID"]);
                result.IDNumber = dr["IDNumber"].ToString();
                result.Name = dr["Name"].ToString();
                result.CompanyName = dr["CompanyName"].ToString();
                result.BeginDate = Convert.ToDateTime(dr["BeginDate"]);
                result.Reason = dr["Reason"].ToString();
            }
            return result;
        }
        /// <summary>
        /// 通过ID取得FlowType
        /// </summary>
        /// <param name="_id">流程内部编码</param>
        /// <returns></returns>
        public static FlowType LoadByFlowID(int _id)
        {
            FlowType ft = new FlowType();
            string sqlCmd;
            sqlCmd = "select * from t_FlowType where FlowID=" + _id.ToString();
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                ft.FlowID = _id;
                ft.FlowCode = dr["FlowCode"].ToString();
                ft.FlowName = dr["FlowName"].ToString();
                ft.FlowDescription = dr["FlowDescription"].ToString();
                ft.IsLimit = Convert.ToBoolean(dr["IsLimited"]);
                ft.IsTimeLimited = Convert.ToBoolean(dr["IsTimeLimited"]);
                ft.IsFirstTimeLimited = Convert.ToBoolean(dr["IsFirstTimeLimited"]);
                ft.FirstLimitedTime = Convert.ToInt32(dr["FirstLimitedTime"]);
                if (DBNull.Value.Equals(dr["LimitedTime"]))
                {
                    ft.LimitedTime = -1;
                }
                else
                {
                    ft.LimitedTime = Convert.ToInt32(dr["LimitedTime"]);
                }
            }
            return ft;
        }

        /// <summary>
        /// 取得下一节点列表
        /// </summary>
        /// <returns></returns>
        public static List<int> GetNextSiteIDList(string _issueNumber)
        {
            string sqlCmd;
            List<int> result = new List<int>();

            
            if (LoadByFlowID(GetIssuedCardByNumber(_issueNumber).FlowID).IsLimit)
            {
                int NextFlowOrder = GetNextFlowOrder(_issueNumber);
                if (NextFlowOrder < 0)
                {
                    return result;
                }
                sqlCmd = "if (select top 1 IsOrderID" +
                    " from t_Flow f,t_IssuedCard i ,t_ICCard C" +
                    " where i.FlowID=f.FlowID" +
                    " and i.CardID=C.CardID" +
                    " and i.IssueNumber='" + _issueNumber.Trim() + "' and FlowOrder=" + NextFlowOrder.ToString() + ")=1" +
                    " BEGIN" +
                    " select identity(int) id0,* into #temp from t_ReadRecord where IssueNumber='" + _issueNumber.Trim() + "' and Status=11 order by TimeStamp " +
                    " select siteID" +
                    " from #temp" +
                    " where id0=(select COUNT(distinct floworder)" +
                    " from t_Flow f,t_IssuedCard i" +
                    " where i.FlowID=f.FlowID" +
                    " and i.IssueNumber='" + _issueNumber.Trim() + "' and FlowOrder<=" +
                    " (select f.SiteID from t_Flow f,t_IssuedCard i where i.FlowID=f.FlowID and i.IssueNumber='" + _issueNumber.Trim() + "' and FlowOrder=" + NextFlowOrder.ToString() + "))" +
                    " drop table #temp" +
                    " END" +
                    " ELSE" +
                    " select f.SiteID " +
                    " from t_Flow f,t_IssuedCard i,t_ICCard C" +
                    " where i.FlowID=f.FlowID" +
                    " and i.CardID=C.CardID" +
                    " and i.IssueNumber='" + _issueNumber.Trim() + "'" +
                    " and f.FlowOrder=" + NextFlowOrder.ToString();
            }
            else
            {
                sqlCmd = "select siteID from t_site where Enabled=1";
            }
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            while (dr.Read())
            {
                result.Add(Convert.ToInt32(dr[0]));
            }
            return result;
        }

        /// <summary>
        /// 取得此流程下一顺序号
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <returns></returns>
        public static int GetNextFlowOrder(string _issueNumber)
        {
            int result = -1;
            string sqlCmd = "select identity(int) id0,* into #temp from" +
                 " (select distinct f.floworder from t_flow f,t_IssuedCard i where i.FlowID=f.FlowID and i.IssueNumber='" + _issueNumber.Trim() + "') a" +
                 " select FlowOrder from #temp" +
                 " where id0=(select COUNT(*)+1 from t_ReadRecord where IssueNumber='" + _issueNumber.Trim() + "' and (Status=11 or Status=5))" +
                  " drop table #temp";
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToInt32(dr[0]);
            }
            return result;
        }

        /// <summary>
        /// 取得此流程下一顺序号的状态
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <returns></returns>
        public static bool GetFlowOrderStatus(string _issueNumber)
        {
            bool result = false;
            int floworder=GetNextFlowOrder( _issueNumber);
            string sqlCmd = "select isupdatestatus from t_flow f,t_IssuedCard i where i.flowid=f.flowid and i.IssueNumber='" + _issueNumber.Trim() + "' and f.floworder=" + floworder ;
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result =Convert .ToBoolean (dr[0]);
            }
            return result;
        }

        /// <summary>
        /// 取得此流程下一顺序号是否需要更新车位
        /// </summary>
        /// <param name="_issueNumber"></param>
        /// <returns></returns>
        public static bool GetFlowOrderParking(string _issueNumber)
        {
            bool result = false;
            int floworder = GetNextFlowOrder(_issueNumber);
            string sqlCmd = "select isupdateparking from t_flow f,t_IssuedCard i where i.flowid=f.flowid and i.IssueNumber='" + _issueNumber.Trim() + "' and f.floworder=" + floworder;
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToBoolean(dr[0]);
            }
            return result;
        }
        /// <summary>
        /// 取得读卡点信息
        /// </summary>
        /// <param name="_id"></param>
        /// <returns></returns>
        public static Site LoadBySiteID(int _id)
        {
            Site result = new Site();
            string sqlCmd;
            sqlCmd = "select * from t_site where siteID=" + _id.ToString();
            DataTableReader dr = ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result.SiteID = _id;
                result.SiteCode = dr["SiteCode"].ToString();
                result.SiteName = dr["siteName"].ToString();
                result.SiteDescription = dr["SiteDescription"].ToString();
                result.Passtimes = Convert.ToInt32(dr["Passtimes"]);
            }
            return result;
        }
        #endregion

        #region 门店
        /// <summary>
        /// 根据SQL语句取得门店（程序内部调用，用户不可见）
        /// </summary>
        /// <param name="SqlCmd">Sql语句</param>
        /// <returns>门店，未找到返回null</returns>
        private static Store StoreGet(string SqlCmd)
        {
            Store result = null;
            DataTableReader dr = ds.GetRecord(SqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = new Store();
                result.StoreID = Convert.ToInt32(dr["StoreID"]);
                result.StoreCode = dr["StoreCode"].ToString();
                result.StoreName = dr["StoreName"].ToString();
                result.StoreAddress = dr["StoreAddress"].ToString();
                result.AreaID = Convert.ToInt32(dr["AreaID"]);
                result.CarrierID = Convert.ToInt32(dr["CarrierID"]);
            }
            return result;
        }

        /// <summary>
        /// 根据门店編号取得该门店
        /// </summary>
        /// <param name="_doNumber">门店編号</param>
        /// <returns>门店</returns>
        public static Store StoreGetByCode(string _storeCode)
        {
            string SqlCmd;
            SqlCmd = "Select * from t_Store where StoreCode='" + _storeCode.Trim() + "'";
            return StoreGet(SqlCmd);
        }
        #endregion

        #region 供货商
        /// <summary>
        /// 取得供货商列表
        /// </summary>
        /// <param name="IsOnlyPrintDo">是否只取打印提单客户</param>
        /// <returns>列表数据集</returns>
        public static DataView SupplierGetList(bool IsOnlyPrintDO)
        {
            string SqlCmd;
            SqlCmd = "select * from t_Supplier";
            if (IsOnlyPrintDO)
            {
                SqlCmd = SqlCmd + " where IsNeedPrintDO=1";
            }
            return ds.GetRecord(SqlCmd).Tables[0].DefaultView;
        }
        /// <summary>
        /// 取得供货商ID和shortname
        /// </summary>
        /// <returns>供货商ID和shortname信息</returns>
        public static DataTable GetSuppierInfo()
        {
            string sqlCmd;
            sqlCmd = "select SupplierID,ShortName from t_Supplier order by supplierid";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

        /// <summary>
        /// 取得供货商ID和shortname
        /// </summary>
        /// <returns>供货商ID和shortname信息</returns>
        public static DataTable GetSuppierSuppierInfo()  
        {
            string sqlCmd;
            sqlCmd = "select [SupplySupplierID],[SupplySupplierName] from [t_SupplySupplier] order by SupplySupplierID";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

        #endregion

        #region 车位
        /// <summary>
        /// 取得B区域车位总数
        /// </summary>
        /// <returns>车位数</returns>
        public static int GetParking()
        {
            int result = 0;
            string sqlCmd;

            sqlCmd = "select COUNT(ParkingID) from t_Parking where AreaID =2";
            DataTableReader dtrCard = Ds.GetRecord(sqlCmd).CreateDataReader();
            dtrCard.Read();
            if (dtrCard[0].ToString() == string.Empty)
            {
                result = 0;
            }
            else
            {
                result = int.Parse(dtrCard[0].ToString());
            }
            return result;
        }
        /// <summary>
        /// 取得B区域已占用车位数
        /// </summary>
        /// <returns>已占用车位数</returns>
        public static int GetUsedParking()
        {
            int result = 0;
            string sqlCmd;

            sqlCmd = "select COUNT(MergeNumber) from t_MO where IsFinished =0 and VehicleSite is not NULL";
            DataTableReader dtrCard = Ds.GetRecord(sqlCmd).CreateDataReader();
            dtrCard.Read();
            if (dtrCard[0].ToString() == string.Empty)
            {
                result = 0;
            }
            else
            {
                result = int.Parse(dtrCard[0].ToString());
            }
            return result;
        }

        /// <summary>
        /// 取得B区域空车位总数
        /// </summary>
        /// <returns>空车位数据集</returns>
        public static DataTable GetNullParking()
        {
            string sqlCmd;
            sqlCmd = "select ParkingName  from t_Parking" +
                    " where AreaID =2 and ParkingName not in" +
                    " (select ParkingName  from t_MO where IsFinished ='False' and ParkingName is not null)";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 取得车辆周转率数据
        /// </summary>
        /// <returns>车辆周转数据集</returns>
        public static DataTable GetVehTurnOver(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select a.shortname,b.vehcount ,a.vehsum"+
                      " from (select c.ShortName ,COUNT(ve.vehiclenumber) as vehsum"+
                            " from t_VehicleInfo ve,t_Carrier c"+ 
                            " where ve.IsChecked =1 and ve.CarrierID =c.CarrierID"+
                            " group by c.ShortName ) as a,"+
                            " (select c.shortname,count(i.VehicleNumber)as vehcount"+ 
                            " from t_Carrier c,t_IssuedCard i,t_VehicleInfo v"+
                            " where i.VehicleNumber =v.VehicleNumber and v.CarrierID =c.CarrierID and v.IsChecked =1"+sqlcase + 
                            " group by c.ShortName) as b"+
                            " where a.ShortName =b.ShortName ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

         /// <summary>
        /// 取得车辆周转率数据明细
        /// </summary>
        /// <returns>车辆周转数据集</returns>
        public static DataTable GetVehTurnOverDist(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select c.shortname,i.VehicleNumber ,count(i.VehicleNumber)as vehcount " +
                    "from t_Carrier c,t_IssuedCard i,t_VehicleInfo v " +
                    "where i.VehicleNumber =v.VehicleNumber and v.CarrierID =c.CarrierID and v.IsChecked =1 " + sqlcase +
                    " group by c.ShortName,i.VehicleNumber " +
                    " order by c.ShortName";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

    
        #endregion

        #region 运费计算
        /// <summary>
        /// 取得吨位区间信息
        /// </summary>
        /// <returns>吨位区间数据集</returns>
        public static DataTable GetWeightGrade()
        {
            string sqlCmd;
            sqlCmd ="select Tid as 编号,Tmin as 下限值, Tmax as 上限值,Describe as 描述 "+
                    "from t_WeightGrade "+
                    "order by Tid ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 检查提单是否存在
        /// </summary>
        /// <returns>吨位区间数据集</returns>
        public static bool checkFareDoNumber(string donumber)
        {
            bool result;
            string sqlstr = "select deliverynumber from t_faredo where deliverynumber ='" + donumber + "'";
            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result = false;
            }
            else
            {
                result = true;
            }
            return result;
        }
        /// <summary>
        /// 新增吨位区间信息
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveWeightgrade(string tmin, string tmax, string desc)
        {
            bool result = true;
            string sqlCmd;
            sqlCmd = "insert t_WeightGrade (tmin,tmax,Describe) values ('" + tmin + "','" + tmax + "','" + desc + "')";
            ds.ExecuteSQL(sqlCmd);
            return result;
        }
        /// <summary>
        /// 取得吨位区间id信息
        /// </summary>
        /// <returns>吨位区间id和描述列数据集</returns>
        public static DataTable GetWeightGradeId()
        {
            string sqlCmd;
            sqlCmd = "select Tid ,Describe " +
                    "from t_WeightGrade " +
                    "order by Tid ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 保存应收运费价格体系信息
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveFareIn(string areaid, string tid, string type,string price)
        {
            bool result = true;
            string sqlCmd;
            sqlCmd = "insert t_ReceivablePrice (areaid,tid,type,price) values ('" + areaid + "','" + tid + "','" + type+ "','"+price +"')";
            ds.ExecuteSQL(sqlCmd);
            return result;
        }
        /// <summary>
        /// 取得应付运费价格体系信息
        /// </summary>
        /// <returns>价格体系数据集</returns>
        public static DataTable GetReceivablePrice(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select rp.PriceID 编号,rp.Type 类型,ar.AreaName 区域,wg.Describe 价格区间,rp.Price 价格" +
                    " from t_Area ar,t_ReceivablePrice rp,t_WeightGrade wg" +
                    " where ar.AreaID =rp.AreaID and rp.Tid =wg.Tid" + sqlcase;
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 保存应付运费价格体系信息
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveFareOut(string areaid, string tid,string prodtype, string type, string price)
        {
            bool result = true;
            string sqlCmd;
            sqlCmd = "insert t_Payables (areaid,tid,prodtypeid,type,price) values ('" + areaid + "','" + tid + "','"+prodtype +"','" + type + "','" + price + "')";
            ds.ExecuteSQL(sqlCmd);
            return result;
        }
        public static DataTable Receivable(string orderdate,string address)
        {
            string sqlCmd;
            sqlCmd ="select rp.AreaID ,rp.Type ,wg.Tmin,wg.Tmax ,rp.Price ,a.weight "+
                    " from t_ReceivablePrice rp,t_WeightGrade wg,( select d.AreaID,d.SaleType ,floor(sum(de.weight)) as weight from t_FareDO d,t_FareDOEntry de where d.DeliveryNumber =de.DeliveryNumber and d.DeliveryAddress ='" + address + "' and d.OrderDatetime ='" + orderdate + "'" +
                   " group by d.AreaID,d.SaleType) a"+
                   " where rp.AreaID =a.AreaID and rp.Type =a.SaleType and wg.Tid =rp.Tid";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        public static DataTable GetFareInfo(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select d.DeliveryNumber 提单号 ,d.OrderDatetime 提单日期 ,d.SaleType 类型 ,d.CustName 客户名称,p.Prodname 产品 ,de.OrderQty 数量 ,de.Weight 重量 ,a.areaname 区域 ,d.DeliveryAddress 送货地址 ,c.shortname 承运商" +
                    " from t_FareDO d,t_FareDOEntry de,t_product p,t_area a,t_carrier c " +
                    " where d.DeliveryNumber =de.DeliveryNumber and de.prodid=p.prodid and d.areaid=a.areaid and d.carrierid=c.carrierid " + sqlcase +
                    " order by d.OrderDatetime ,d.DeliveryAddress ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        #endregion

        #region 库存管理
        /// <summary>
        /// 取得当前账期 
        /// </summary>
        /// <returns>当前账期值</returns>
        public static string GetAccountPeriod()
        {
            string result="";
            string sqlCmd;
            sqlCmd = "select AccountPeriod from t_AccountPeriod where Isclosed=0";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToString (dr[0]);
            }
            return result;
        }
        /// <summary>
        /// 保存关帐信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool ClosedAccount(string accountperiod,string userid,string nextaccountperiod)
        {
            string sqlCmd;
            sqlCmd = "update t_AccountPeriod set userid='" + userid + "', ClosedDate=GETDATE(),Isclosed=1 where AccountPeriod='" + accountperiod + "'" +
                    " insert t_AccountPeriod (AccountPeriod) values ('" + nextaccountperiod + "')";
            return ds.ExecuteSQL(sqlCmd);
        }
    
    
        /// <summary>
        /// 取得下一个账期 
        /// </summary>
        /// <returns>账期值</returns>
        public static string GetNextAccountPeriod()
        {
            string accountperiod = GetAccountPeriod();
            string nextaccountperiod = "";
            int year = Int32.Parse(accountperiod.Substring(0, 4));
            int mm = Int32.Parse(accountperiod.Substring(4, 2));
            if (mm == 12)
            {
                nextaccountperiod = (year +1).ToString () + "01";
            }
            else
            {
                nextaccountperiod = year.ToString() + (mm + 1).ToString("00");
            }
            return nextaccountperiod;
        }
        /// <summary>
        ///  反关帐
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool UnCloseAccount(string accountperiod)
        {
            string sqlCmd;
            sqlCmd = "delete t_AccountPeriod where AccountPeriod='" + accountperiod + "'" +
                     " update t_AccountPeriod set isclosed=0 where AccountPeriod in (select MAX(AccountPeriod) " +
                     "from t_AccountPeriod " +
                     "where Isclosed =1)";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 取得当前账期的最后一天
        /// </summary>
        /// <returns>当前账期最后一天的日期字符串</returns>
        public static string GetLastDay(string accountperiod)
        {
            int year =Int32 .Parse (accountperiod.Substring(0, 4));
            int mm = Int32.Parse(accountperiod.Substring(4, 2));
            int days = DateTime.DaysInMonth(year, mm);
            DateTime datetime = new DateTime(year, mm, 1);
            return datetime.AddDays(days-1).ToString ("yyyyMMdd");

            
        }
        /// <summary>
        /// 获取关帐库存数据
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetNowStock(string fristday, string lastday,string accountperiod)
        {
            string sqlCmd;
            sqlCmd = "select a.StorageID ,a.SupplierID ,a.ProdID ,a.Batch ,sum(a.qty ) as qty " +
                     "from " +
                     "(select ie.StorageID ,ie.SupplierID ,ie.ProdID ,ie.Batch ,SUM(ie.InStockQty ) as qty " +
                     "from t_InStock ik,t_InStockEntry ie " +
                     "where ik.InStockNumber =ie.InStockNumber and ik.InStockDate >='" + fristday + "' and ik.InStockDate <='" +lastday +"' "+
                     "group by ie.StorageID ,ie.ProdID ,ie.Batch ,ie.SupplierID " +
                     "union all " +
                     "select ee.InStorageID ,ee.SupplierID ,ee.ProdID ,ee.Batch ,SUM(ee.ExQty ) as qty " +
                     "from t_ExchangeStock es,t_ExchangeStockEntry ee " +
                     "where es.ExStockNumber =ee.ExStockNumber and es.ExDate >='" + fristday + "' and es.ExDate <='" + lastday + "' " +
                     "group by ee.InStorageID ,ee.SupplierID ,ee.ProdID ,ee.Batch " +
                     "union all " +
                     "select oe.StorageID ,oe.SupplierID ,oe.ProdID ,oe.Batch ,0-SUM(oe.OutStockQty ) as qty " +
                     "from t_OutStock os,t_OutStockEntry oe " +
                     "where os.OutStockNumber =oe.OutStockNumber and os.OutDate >='" + fristday + "' and os.OutDate <='" + lastday + "' " +
                     "group by oe.StorageID ,oe.SupplierID ,oe.ProdID ,oe.Batch " +
                     "union all " +
                     "select ee.OutStorageID ,ee.SupplierID ,ee.ProdID ,ee.Batch ,0-SUM(ee.ExQty ) as qty " +
                     "from t_ExchangeStock es,t_ExchangeStockEntry ee " +
                     "where es.ExStockNumber =ee.ExStockNumber and es.ExDate >='" + fristday + "' and es.ExDate <='" + lastday + "' " +
                     "group by ee.OutStorageID ,ee.SupplierID ,ee.ProdID ,ee.Batch " +
                     "union all " +
                     "select StorageID ,SupplierID ,ProdID ,Batch ,sum(StockQty ) as qty " +
                     "from t_OpeningInventory " +
                     "where AccountPeriod='"+accountperiod +"' "+
                     "group by StorageID ,SupplierID ,ProdID ,Batch ) a " +
                     "group by a.StorageID ,a.SupplierID ,a.ProdID ,a.Batch ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0]; 
        }
        /// <summary>
        /// 获取库存数据(按品项）
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetStockByProdid(string fristday, string lastday, string accountperiod,string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select st.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ,sum(a.qty ) as qty " +
                     "from " +
                     "(select ie.StorageID ,ie.SupplierID ,ie.ProdID ,SUM(ie.InStockQty ) as qty " +
                     "from t_InStock ik,t_InStockEntry ie " +
                     "where ik.InStockNumber =ie.InStockNumber and ik.InStockDate >='" + fristday + "' and ik.InStockDate <='" + lastday + "' " +
                     "group by ie.StorageID ,ie.ProdID ,ie.SupplierID " +
                     "union all " +
                     "select ee.InStorageID ,ee.SupplierID ,ee.ProdID ,SUM(ee.ExQty ) as qty " +
                     "from t_ExchangeStock es,t_ExchangeStockEntry ee " +
                     "where es.ExStockNumber =ee.ExStockNumber and es.ExDate >='" + fristday + "' and es.ExDate <='" + lastday + "' " +
                     "group by ee.InStorageID ,ee.SupplierID ,ee.ProdID " +
                     "union all " +
                     "select oe.StorageID ,oe.SupplierID ,oe.ProdID ,0-SUM(oe.OutStockQty ) as qty " +
                     "from t_OutStock os,t_OutStockEntry oe " +
                     "where os.OutStockNumber =oe.OutStockNumber and os.OutDate >='" + fristday + "' and os.OutDate <='" + lastday + "' " +
                     "group by oe.StorageID ,oe.SupplierID ,oe.ProdID " +
                     "union all " +
                     "select ee.OutStorageID ,ee.SupplierID ,ee.ProdID ,0-SUM(ee.ExQty ) as qty " +
                     "from t_ExchangeStock es,t_ExchangeStockEntry ee " +
                     "where es.ExStockNumber =ee.ExStockNumber and es.ExDate >='" + fristday + "' and es.ExDate <='" + lastday + "' " +
                     "group by ee.OutStorageID ,ee.SupplierID ,ee.ProdID " +
                     "union all " +
                     "select StorageID ,SupplierID ,ProdID ,sum(StockQty ) as qty " +
                     "from t_OpeningInventory " +
                     "where AccountPeriod='" + accountperiod + "' " +
                     "group by StorageID ,SupplierID ,ProdID ) a ,t_Product pd,t_Storage st,t_Supplier sp " +
                     "where a.ProdID =pd.ProdID and a.StorageID =st.StorageID and a.SupplierID =sp.SupplierID "+sqlcase +
                     " group by st.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

         /// <summary>
        /// 获取库存数据(库位级别）
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetStockkuwei(string sqlcase)
        {
            StringBuilder sqlCmd = new StringBuilder();
            sqlCmd.Append(" select b.ProdCode,b.ProdName,c.ProdTypeName,d.ShortName,e.StoreName,g.KuquName,f.StorageName, ");
            sqlCmd.Append(" a.Batch,a.ProduceDate,a.InputDate,DATEDIFF(DAY,a.InputDate,GETDATE()) huolin,h.StockPinZhiMiaoShu, ");
            sqlCmd.Append(" b.Unit,a.StockQty,(a.StockQty-a.StockUseableQty) dongjie,a.StockUseableQty,(a.StockQty*b.Weight) Weight1,");
            sqlCmd.Append(" ((a.StockQty-a.StockUseableQty)*b.Weight) dongjieWeight1,(a.StockUseableQty*b.Weight) UserbleWeight1, ");
            sqlCmd.Append(" (a.StockQty*b.Volume) Volume1,((a.StockQty-a.StockUseableQty)*b.Volume) dongjieVolume1,(a.StockUseableQty*b.Volume) UserbleVolume1, a.Remark ");
            sqlCmd.Append(" from t_StockTaking a,t_Product b,t_ProductType c,t_Supplier d,t_Store e,t_Storage f ,t_Kuqu g,t_StockPinZhi h ");
            sqlCmd.Append(" where a.ProdID = b.ProdID and b.ProdTypeID = c.ProdTypeID and d.SupplierID =a.SupplierID  ");
            sqlCmd.Append(" and f.StorageID = a.StorageID and g.KuquID = f.KuquID and g.StoreID = e.StoreID and a.StockPinZhiID = h.StockPinZhiID ");
            if(sqlcase!=null&&sqlcase!="")
            {
                sqlCmd.Append(sqlcase);
            }
            sqlCmd.Append(" order by a.InputDate desc ");
            DataSet dt = ds.GetRecord(sqlCmd.ToString());
            return dt.Tables[0];
        }
        /// <summary>
        /// 获取库存数据(仓库级别）
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetStockcangku(string sqlcase)  
        {
            StringBuilder sqlCmd = new StringBuilder();
            sqlCmd.Append(" select b.ProdCode,b.ProdName,c.ProdTypeName,d.ShortName,h.StockPinZhiMiaoShu,sum(a.StockQty) StockQty ,sum(a.StockQty-a.StockUseableQty) dongjie, ");
            sqlCmd.Append("  sum(a.StockUseableQty) StockUseableQty, b.Unit,sum(a.StockQty*b.Weight) Weight1,  ");
            sqlCmd.Append("  sum((a.StockQty-a.StockUseableQty)*b.Weight) dongjieWeight1,sum(a.StockUseableQty*b.Weight) UserbleWeight1, ");
            sqlCmd.Append(" sum(a.StockQty*b.Volume) Volume1 ,sum((a.StockQty-a.StockUseableQty)*b.Volume) dongjieVolume1, ");
            sqlCmd.Append(" sum(a.StockUseableQty*b.Volume) UserbleVolume1 from t_StockTaking a,t_Product b,t_ProductType c,t_Supplier d ");
            sqlCmd.Append("    ,t_StockPinZhi h  where a.ProdID = b.ProdID and b.ProdTypeID = c.ProdTypeID and   ");
            sqlCmd.Append(" d.SupplierID =a.SupplierID   and a.StockPinZhiID = h.StockPinZhiID   ");
            
            if (sqlcase != null && sqlcase != "")
            {
                sqlCmd.Append(sqlcase);
            }
            sqlCmd.Append("  group by b.ProdCode,b.ProdName,c.ProdTypeName,d.ShortName,h.StockPinZhiMiaoShu,b.Unit  ");
            sqlCmd.Append(" order by b.ProdCode desc ");
            DataSet dt = ds.GetRecord(sqlCmd.ToString());
            return dt.Tables[0];
        }


        /// <summary>
        /// 获取越库采购报表
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable Getyuekucaigou(string where)      
        {
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select  a.CaigouNumber,b.SupplySupplierName,b.dizhi,d.fukuanmiaoshu,c.shouhuoshuxinmiaoshu,b.yinhangzhanghao   ");
            sqlstr1.Append(" ,b.lianxifangshi,e.StoreAddress,a.CaigouDate,g.ProdTypeName,h.ProdType,f.CaigouShuLiang,f.Caigoujia,f.CaigouShuLiang*f.Caigoujia jine,f.Remark,h.ProdName,h.ProdCode   ");
            sqlstr1.Append("  from t_Caigou a ,t_SupplySupplier b,t_shouhuoshuxing c,t_fukuan d,t_Store e,t_CaigouEntry f,[t_ProductType] g ,[t_Product] h  ");
            sqlstr1.Append(" where a.SupplySupplierID = b.SupplySupplierID and b.shouhuoshuxingID = c.shouhuoshuxingID    ");
            sqlstr1.Append(" and b.fukuanID = d.fukuanID and a.StoreID = e.StoreID  and a.CaigouID = f.CaigouID   ");
            sqlstr1.Append(" and g.[ProdTypeID]=h.[ProdTypeID] and f.[ProdID] = h.[ProdID]   ");

            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }
            //sqlstr1.Append("  group by b.ProdCode,b.ProdName,c.ProdTypeName,d.ShortName,h.StockPinZhiMiaoShu,b.Unit  ");
            sqlstr1.Append(" order by a.CaigouNumber asc,h.ProdID asc ");
            DataSet dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];
        }

        /// <summary>
        /// 获取库存数据(按库位）
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetStockByStroage(string fristday, string lastday, string accountperiod, string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select st.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ,a.Batch,sum(a.qty ) as qty " +
                     "from " +
                     "(select ie.StorageID ,ie.SupplierID ,ie.ProdID ,ie.Batch ,SUM(ie.InStockQty ) as qty " +
                     "from t_InStock ik,t_InStockEntry ie " +
                     "where ik.InStockNumber =ie.InStockNumber and ik.InStockDate >='" + fristday + "' and ik.InStockDate <='" + lastday + "' " +
                     "group by ie.StorageID ,ie.ProdID ,ie.SupplierID,ie.Batch " +
                     "union all " +
                     "select ee.InStorageID ,ee.SupplierID ,ee.ProdID ,ee.Batch,SUM(ee.ExQty ) as qty " +
                     "from t_ExchangeStock es,t_ExchangeStockEntry ee " +
                     "where es.ExStockNumber =ee.ExStockNumber and es.ExDate >='" + fristday + "' and es.ExDate <='" + lastday + "' " +
                     "group by ee.InStorageID ,ee.SupplierID ,ee.ProdID,ee.Batch " +
                     "union all " +
                     "select oe.StorageID ,oe.SupplierID ,oe.ProdID ,oe.Batch ,0-SUM(oe.OutStockQty ) as qty " +
                     "from t_OutStock os,t_OutStockEntry oe " +
                     "where os.OutStockNumber =oe.OutStockNumber and os.OutDate >='" + fristday + "' and os.OutDate <='" + lastday + "' " +
                     "group by oe.StorageID ,oe.SupplierID ,oe.ProdID,oe.Batch  " +
                     "union all " +
                     "select ee.OutStorageID ,ee.SupplierID ,ee.ProdID ,ee.Batch,0-SUM(ee.ExQty ) as qty " +
                     "from t_ExchangeStock es,t_ExchangeStockEntry ee " +
                     "where es.ExStockNumber =ee.ExStockNumber and es.ExDate >='" + fristday + "' and es.ExDate <='" + lastday + "' " +
                     "group by ee.OutStorageID ,ee.SupplierID ,ee.ProdID,ee.Batch " +
                     "union all " +
                     "select StorageID ,SupplierID ,ProdID ,Batch,sum(StockQty ) as qty " +
                     "from t_OpeningInventory " +
                     "where AccountPeriod='" + accountperiod + "' " +
                     "group by StorageID ,SupplierID ,ProdID,Batch ) a ,t_Product pd,t_Storage st,t_Supplier sp " +
                     "where a.ProdID =pd.ProdID and a.StorageID =st.StorageID and a.SupplierID =sp.SupplierID " + sqlcase +
                     " group by st.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType,a.Batch ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 取得库位ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetStorageID(string storagename)
        {
            int result = 0;
            string sqlCmd;
            sqlCmd = "select StorageID from t_Storage where StorageName='" + storagename.Trim() + "'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToInt32(dr[0]);
            }
            return result;
        }

        /// <summary>
        /// 取得库位ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetStorageID(string storagename,SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select StorageID from t_Storage where StorageName='" + storagename.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 取得货权公司ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetSupplierID(string shortname, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select SupplierID from t_Supplier where ShortName='" + shortname.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 取得人员ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetUserID(string UserName, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select UserID from t_User where UserName='" + UserName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 取得入库类型ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetInStockTypeID(string InStockTypemiaoshu, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select InStockTypeID from t_InStockType where InStockTypemiaoshu='" + InStockTypemiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            { 
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 取得入库状态ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetInStockStatusID(string InStockStatusmiaoshu, SqlConnection connection, SqlCommand cmd)
        { 
            string sqlCmd;
            sqlCmd = "select InStockStatusID from t_InStockStatus where InStockStatusmiaoshu='" + InStockStatusmiaoshu.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 取得供应商ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int getSupplySupplierID(string ShortName, SqlConnection connection, SqlCommand cmd) 
        { 
            string sqlCmd;
            sqlCmd = "select SupplySupplierID from t_SupplySupplier where ShortName='" + ShortName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 取得承运商ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int getCarrierID(string ShortName, SqlConnection connection, SqlCommand cmd)
        {
            string sqlCmd;
            sqlCmd = "select CarrierID from t_Carrier where ShortName='" + ShortName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 取得仓库ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int getStoreID(string StoreName, SqlConnection connection, SqlCommand cmd) 
        {
            string sqlCmd;
            sqlCmd = "select StoreID from t_Store where StoreName='" + StoreName.Trim() + "'";
            object obj = App.GetSingle(sqlCmd, null, connection, cmd);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 取得库存调整类型ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetExchangeID(string typename)
        {
            int result = 0;
            string sqlCmd;
            sqlCmd = "select ExchangeID from t_ExchangeType where TypeNames='" + typename.Trim() + "'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToInt32(dr[0]);
            }
            return result;
        }

        /// <summary>
        /// 取得货权公司ID 
        /// </summary>
        /// <returns>ID值</returns>
        public static int GetSupplierID(string shortname)
        {
            int result = 0;
            string sqlCmd;
            sqlCmd = "select SupplierID from t_Supplier where ShortName='" + shortname.Trim() + "'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToInt32(dr[0]);
            }
            return result;
        }

        /// <summary>
        /// 根据产品代码获取产品ID
        /// </summary>
        /// <param name="_prodCode">产品代码</param>
        /// <returns></returns>
        public static int GetProdIDByCode(string _prodCode)
        {
            int result = 0;
            string sqlCmd;
            sqlCmd = "select ProdID from t_Product where ProdCode='" + _prodCode.Trim() + "'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToInt32(dr[0]);
            }
            return result;
        }
        /// <summary>
        /// 保存盘点数据 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveStockTaking(Stock st)
        {
            string sqlCmd;
            sqlCmd = " insert t_StockTaking (InputDate,StorageID,SupplierID,ProdID,Batch,StockQty) values ('" +st.InputDate +"','"
                     + st.StorageID + "','" + st.SupplierID + "','" + st.ProdID + "','" + st.Batch + "','" + st.StockQty + "')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存期初库存数据 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveOpeningInventory(Stock st)
        {
            string sqlCmd;
            sqlCmd = " insert t_OpeningInventory (AccountPeriod,StorageID,SupplierID,ProdID,Batch,StockQty,Remark) values ('"
                     +st.AccountPeriod +"','" + st.StorageID + "','" + st.SupplierID + "','" + st.ProdID + "','" + st.Batch + "','" + st.StockQty + "','"+st.Remark +"')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 删除期初库存数据 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool DelOpeningInventory(string accountperiod)
        {
            string sqlCmd;
            sqlCmd = "delete t_OpeningInventory where AccountPeriod='" + accountperiod + "'";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存期末库存数据 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveEndingInventory(string accountperiod, Stock st)
        {
            string sqlCmd;
            sqlCmd = " insert t_EndingInventory (AccountPeriod,StorageID,SupplierID,ProdID,Batch,StockQty) values ('"
                     + accountperiod + "','" + st.StorageID + "','" + st.SupplierID + "','" + st.ProdID + "','" + st.Batch + "','" + st.StockQty + "')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 删除期末库存数据 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool DelEndingInventory(string accountperiod)
        {
            string sqlCmd;
            sqlCmd = "delete t_EndingInventory where AccountPeriod='" + accountperiod + "'";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 检查入库单是否存在 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool CheckInStockNumber(string instocknumber)
        {
            bool result;
            string sqlstr = " select * from t_InStock where InStockNumber='" + instocknumber + "'";

            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result = false;
            }
            else
            {
                result = true;
            }
            return result;
        }
        /// <summary>
        /// 检查出库单是否存在 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool CheckOutStockNumber(string outstocknumber)
        {
            bool result;
            string sqlstr = " select * from t_OutStock where OutStockNumber='" + outstocknumber + "'";

            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result = false;
            }
            else
            {
                result = true;
            }
            return result;
        }
        /// <summary>
        /// 检查调整单是否存在 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool CheckExStockNumber(string exstocknumber)
        {
            bool result;
            string sqlstr = " select * from t_ExchangeStock where ExStockNumber='" + exstocknumber + "'";

            DataTableReader dr = ds.GetRecord(sqlstr).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                result = false;
            }
            else
            {
                result = true;
            }
            return result;
        }
        /// <summary>
        /// 保存入库单头部信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveInStockHead(StockManage sm)
        {
            string sqlCmd;
            sqlCmd = "insert t_InStock (InStockNumber,InStockDate) values ('" + sm.StockNumber + "','" + sm.StockDate + "')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存入库单明细信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveInStockEntry(StockManage sm)
        {
            string sqlCmd;
            sqlCmd = "insert t_InStockEntry (InStockNumber,StorageID,SupplierID,ProdID,Batch,InStockQty) values ('" + 
                      sm.StockNumber + "','" + sm.StorageID  + "','"+sm.SupplierID +"','"+sm.ProdID +"','"+sm.Batch +"','"+sm.InStockQty +"')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 删除入库单信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool DelInStock(string instocknumber)
        {
            string sqlCmd;
            sqlCmd = "delete t_InStock where InStockNumber='" + instocknumber + "'" +
                     " delete t_InStockEntry where InStockNumber='" + instocknumber + "'";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存出库单头部信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveOutStockHead(StockManage sm)
        {
            string sqlCmd;
            sqlCmd = "insert t_OutStock (OutStockNumber,OutDate) values ('" + sm.StockNumber + "','" + sm.StockDate + "')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存出库单明细信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveOutStockEntry(StockManage sm)
        {
            string sqlCmd;
            sqlCmd = "insert t_OutStockEntry (OutStockNumber,StorageID,SupplierID,ProdID,Batch,OutStockQty,type) values ('" +
                      sm.StockNumber + "','" + sm.StorageID + "','" + sm.SupplierID + "','" + sm.ProdID + "','" + sm.Batch + "','" + sm.InStockQty + "','"+sm.Type +"')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 删除出库单信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool DelOutStock(string outstocknumber)
        {
            string sqlCmd;
            sqlCmd = "delete t_OutStock where OutStockNumber='" + outstocknumber + "'" +
                     " delete t_OutStockEntry where OutStockNumber='" + outstocknumber + "'";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存库存调整单头部信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveExStockHead(StockManage sm)
        {
            string sqlCmd;
            sqlCmd = "insert t_ExchangeStock (ExStockNumber,ExDate,ExchangeID) values ('" + sm.StockNumber + "','" + sm.StockDate + "','"+sm.ExchangeID +"')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 保存库存调整单明细信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool SaveExStockEntry(StockManage sm)
        {
            string sqlCmd;
            sqlCmd = "insert t_ExchangeStockEntry (ExStockNumber,OutStorageID,InStorageID,SupplierID,ProdID,Batch,ExQty) values ('" +
                      sm.StockNumber + "','" + sm.OutStorageID  + "','"+sm.InStorageID +"','" + sm.SupplierID + "','" + sm.ProdID + "','" + sm.Batch + "','" + sm.ExQty + "')";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 删除调整单信息 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool DelExStock(string exstocknumber)
        {
            string sqlCmd;
            sqlCmd = "delete t_ExchangeStock where ExStockNumber='" + exstocknumber + "'" +
                     " delete t_ExchangeStockEntry where ExStockNumber='" + exstocknumber + "'";
            return ds.ExecuteSQL(sqlCmd);
        }
        /// <summary>
        /// 取得调整库存类型ID和名称
        /// </summary>
        /// <returns>类型ID和名称信息</returns>
        public static DataTable GetExType()
        {
            string sqlCmd;
            sqlCmd = "select ExchangeID,TypeNames from t_ExchangeType order by ExchangeID";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 获取盘点报表明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetStockTaking(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select st.InputDate ,sg.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ,st.Batch ,st.StockQty " +
                    "from t_StockTaking st,t_Supplier sp,t_Storage sg,t_Product pd " +
                    "where st.StorageID =sg.StorageID and st.ProdID =pd.ProdID and st.SupplierID =sp.SupplierID " + sqlcase +
                    " order by st.InputDate";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
        /// <summary>
        /// 获取出库单明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetOutStock(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select os.OutDate,os.OutStockNumber,sg.StorageName ,oe.Type,sp.ShortName ,pd.ProdName ,pd.ProdType ,oe.Batch ,oe.OutStockQty " +
                    "from t_OutStock os,t_OutStockEntry oe,t_Supplier sp,t_Storage sg,t_Product pd " +
                    "where os.OutStockNumber =oe.OutStockNumber and oe.StorageID =sg.StorageID and oe.ProdID =pd.ProdID and oe.SupplierID =sp.SupplierID " + sqlcase +
                    " order by os.OutDate";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

        /// <summary>
        /// 获取出库单明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetOutStockmingxi(string sqlcase) 
        {
            string sqlCmd;
            sqlCmd = "select os.OutDate,os.OutStockNumber,sg.StorageName ,oe.Type,sp.ShortName ,pd.ProdName ,pd.ProdType ,oe.Batch ,oe.OutStockQty " +
                    "from t_OutStock os,t_OutStockEntry oe,t_Supplier sp,t_Storage sg,t_Product pd " +
                    "where os.OutStockNumber =oe.OutStockNumber and oe.StorageID =sg.StorageID and oe.ProdID =pd.ProdID and oe.SupplierID =sp.SupplierID " + sqlcase +
                    " order by os.OutDate";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

        /// <summary>
        /// 获取出库单明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetOutStockmingxi11111(string sqlcase) 
        {
            StringBuilder sqlCmd = new StringBuilder();
            sqlCmd.Append(" SELECT distinct  a.[SupplierID],a.[DeliveryNumber],a.[OrderNumber],a.[OrderType],a.[SaleType],a.[PONumber],a.[OrderDatetime],a.[SoldTo],a.[DeliveryTo],a.[CustName] ");
            sqlCmd.Append(" ,a.[DeliveryAddress],a.[CarrierID],a.[VehicleNumber],a.[Remark],a.[StatusID],a.[InStockTime],a.[OutStockTime],a.[AreaID],a.[ArrTimeID],a.[VehicleOrder] ");
            sqlCmd.Append(" ,a.[VehicleDate],a.[CustCode],a.[BusinessMan],a.[PrintTimes],a.[CanBePrinted],a.[Stock],a.[Tihuofangshi],a.[Lianxifangshi],a.[Lianxidianhua] ");
            sqlCmd.Append(" ,a.[Fapiao],a.[Xiadancishu],a.[Zhifuxinxi],a.[youhui] ");
            sqlCmd.Append(" ,b.[ProdID],b.[OrderQty],b.[SentQty],b.[UnitWeight],b.[Weight],b.[Price],b.[NoTaxAmt],b.[Tax],b.[Amount],b.[Batch],b.[DOEntryID] ");
            sqlCmd.Append("  ,c.ShortName CarrierName,d.[ShortName] SupplierName,e.[StatusName],f.[AreaName],g.[DriverName] ");
            sqlCmd.Append("  ,j.[ProdCode],j.[ProdName],j.[Volume],j.[Weight],j.[jianhuoshuxin],k.[ProdTypeName],j.[Unit],g.[DriverMobile] ");
            sqlCmd.Append("  ,l.[StockQty],l.[Batch],l.[ProduceDate],o.[StockPinZhiMiaoShu],m.[KuquName],n.[StorageName],p.[MergeNumber] ");
            sqlCmd.Append("   FROM [t_DO] a left join [t_VehicleInfo] h on a.[VehicleNumber] = h.[VehicleNumber]  left join [t_VehicleDriver] i on h.[VehicleID] = i.[VehicleID] left join [t_DriverInfo] g on g.[DriverID] = i.[DriverID] left join [t_MOEntry] p  on  a.[DeliveryNumber] = p.[DONumber]    ,[t_DOEntry] b left join [t_chukukucun] l on b.[DOEntryID] = l.[DOEntryID] left join  [t_Storage] n on l.[StorageID] = n.[StorageID] left join [t_Kuqu] m on  n.[KuquID] = m.[KuquID]  left join [t_StockPinZhi] o  on  l.[StockPinZhiID] = o.[StockPinZhiID]  ,[t_Carrier] c,[t_Supplier] d ,[t_DOStatus] e,[t_Area] f    ,[t_Product] j,[t_ProductType] k   ");
            sqlCmd.Append("   where a.[DeliveryNumber] = b.[DeliveryNumber] and a.[CarrierID] = c.[CarrierID] and a.[SupplierID] = d.[SupplierID]       ");
            sqlCmd.Append("                  and a.[StatusID] = e.[StatusID] and a.[AreaID] = f.[AreaID]      ");
            sqlCmd.Append("      and b.[ProdID] = j.[ProdID] and j.[ProdTypeID] = k.[ProdTypeID]   ");
            if (sqlcase != null && sqlcase != "")
            {
                sqlCmd.Append(sqlcase);
            }
            sqlCmd.Append(" order by a.OrderDatetime,a.DeliveryNumber desc ");
            DataSet dt = ds.GetRecord(sqlCmd.ToString());
            return dt.Tables[0];
        }

        /// <summary>
        /// 获取供应商商品报备表 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetSupplySupplierProduct(string sqlcase)  
        {
            StringBuilder sqlCmd = new StringBuilder();
            sqlCmd.Append("  SELECT b.[SupplySupplierName],ts.[StorageName],tss.[shouhuoshuxinmiaoshu],tc.[cengjimiaoshu],c.[ProdCode],c.[ProdName] ,c.[ProdType],d.[shangpinshuxingmiaoshu],a.[youxianji],a.[caigoujia],a.[wuliufei],a.[qitafeiyong] ,tf.[fapiaomiaoshu],a.[fandian] ");
         
            sqlCmd.Append("     FROM [t_SupplySupplierProduct] a ,[t_SupplySupplier] b left join [t_Storage] ts on b.[StorageID]=ts.[StorageID]  left join [t_shouhuoshuxing] tss on b.[shouhuoshuxingID] = tss.[shouhuoshuxingID]   left join [t_cengji] tc on b.[cengjiID] = tc.[cengjiID] ,[t_Product] c ,[t_shangpinshuxing] d,[t_fapiao] tf ");
            sqlCmd.Append("   where a.[SupplySupplierID] = b.[SupplySupplierID] and a.[ProdID] = c.[ProdID] and a.[shangpinshuxingID] = d.[shangpinshuxingID] and a.[fapiaoID] = tf.[fapiaoID]    ");
            
            if (sqlcase != null && sqlcase != "")
            {
                sqlCmd.Append(sqlcase);
            }
            sqlCmd.Append(" order  by a.[SupplySupplierID] ");
            DataSet dt = ds.GetRecord(sqlCmd.ToString());
            return dt.Tables[0];
        }
        /// <summary>
        /// 获取出库单报表 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetOutStockrpt(string sqlcase)  
        {
            string sqlCmd;
            sqlCmd = " SELECT a.*,b.*,c.ShortName SupplierName,d.ShortName CarrierName,e.StatusName,g.ProdTypeName,f.* " +
                    "  FROM  [t_DO] a,t_DOEntry b,t_Supplier c ,t_Carrier d , t_DOStatus e , t_Product f , t_ProductType g " +
                    " where  a.DeliveryNumber = b.DeliveryNumber and a.SupplierID = c.SupplierID and a.StatusID = e.StatusID and b.ProdID = f.ProdID and f.ProdTypeID = g.ProdTypeID  " + sqlcase +
                    " order by os.OutDate desc ";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }
         /// <summary>
        /// 获取入库单明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetInStock(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select ik.InStockDate ,ik.InStockNumber,sg.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ,ie.Batch ,ie.InStockQty " +
                    "from t_InStock ik,t_InStockEntry ie,t_Supplier sp,t_Storage sg,t_Product pd " +
                    "where ik.InStockNumber =ie.InStockNumber and ie.StorageID =sg.StorageID and ie.ProdID =pd.ProdID and ie.SupplierID =sp.SupplierID " + sqlcase +
                    " order by ik.InStockDate";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
        }

        /// <summary>
        /// 获取入库单报表
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetInStockrpt(string sqlcase)
        {
            StringBuilder sqlCmd = new StringBuilder();
            sqlCmd.Append(" select ik.InStockNumber ,ik.CaigouNumber,ik.InStockDate,ik.JieDanDate,a.UserName,b.UserName ShouHuoRen, ");
            sqlCmd.Append(" c.InStockTypemiaoshu,d.InStockStatusmiaoshu,pd.ProdCode,pd.ProdName,e.ProdTypeName,sp.ShortName SupplierName, ");
            sqlCmd.Append(" f.ShortName SupplySupplierName ,g.ShortName CarrierName,h.StoreName,sg.StorageName,ie.Batch,ie.ProduceDate, ");
            sqlCmd.Append(" j.StockPinZhiMiaoShu,ie.StockQty,pd.Unit,pd.Weight*ie.StockQty pWeight ,pd.Volume*ie.StockQty pVolume ,ik.TuiHuoKeHu,ik.TuiHuoRemark,ik.Remark  ");
            sqlCmd.Append("   from t_InStock ik left join t_Store h on  h.StoreID = ik.StoreID left join t_User b on  b.UserID =ik.ShouHuoID ,t_InStockEntry ie left join t_Storage sg on ie.StorageID =sg.StorageID left join t_Kuqu i on sg.KuquID = i.KuquID  ,t_Supplier sp,t_Product pd ,t_User a,t_InStockType c,t_InStockStatus d,    t_ProductType e ,t_SupplySupplier f ,t_Carrier g,t_StockPinZhi j   ");

            sqlCmd.Append("  where ik.InStockID =ie.InStockID  and ie.ProdID =pd.ProdID and     ");
            sqlCmd.Append(" ik.SupplierID =sp.SupplierID  and a.UserID =ik.UserID  and ik.InStockTypeID = c.InStockTypeID  ");
            sqlCmd.Append("  and ik.InStockStatusID = d.InStockStatusID and e.ProdTypeID = pd.ProdTypeID and ik.SupplySupplierID = f.SupplySupplierID   ");
            sqlCmd.Append("             and ik.CarrierID = g.CarrierID   and ie.StockPinZhiID = j.StockPinZhiID   ");
            if (sqlcase != null && sqlcase!="")
            {
                sqlCmd.Append(sqlcase);
            }

            sqlCmd.Append(" order by ik.InStockNumber desc  ");
            DataSet dt = ds.GetRecord(sqlCmd.ToString());
            return dt.Tables[0];
        }
        /// <summary>
        /// 获取调整单明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetExstock(string sqlcase)
        {
            string sqlCmd;
            sqlCmd = "select cs.ExDate ,cs.ExStockNumber,et.TypeNames ,sp.ShortName,sg.name1 ,sn.name2 ,pd.ProdName ,pd.ProdType ,ce.Batch ,ce.ExQty " +
                    "from t_ExchangeStock cs,t_ExchangeStockEntry ce,t_Product pd,t_Supplier sp, t_ExchangeType et," +
                    "(select storageid, storagename as name1 from t_Storage ) sg,(select storageid, storagename as name2 from t_Storage ) sn " +
                    "where cs.ExStockNumber =ce.ExStockNumber and cs.ExchangeID =et.ExchangeID  and ce.OutStorageID =sg.StorageID " +
                    "and ce.InStorageID =sn.StorageID and ce.ProdID =pd.ProdID and ce.SupplierID =sp.SupplierID " + sqlcase +
                    " order by cs.ExDate";
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];
 
        }
        /// <summary>
        /// 获取库位进出明细信息 
        /// </summary>
        /// <returns>数据表</returns>
        public static DataTable GetInOutStock(string sqlcase1,string sqlcase2)
        {
            string sqlCmd;
            sqlCmd = "select ik.InStockNumber,sg.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ,ie.InStockQty ,case when ik.InStockNumber is not null then '入库单' end as type " +
                    "from t_InStock ik,t_InStockEntry ie,t_Supplier sp,t_Storage sg,t_Product pd " +
                    "where ik.InStockNumber =ie.InStockNumber and ie.StorageID =sg.StorageID and ie.ProdID =pd.ProdID and ie.SupplierID =sp.SupplierID " + sqlcase1 +
                    " union all " +
                    "select os.OutStockNumber as InStockNumber ,sg.StorageName ,sp.ShortName ,pd.ProdName ,pd.ProdType ,oe.OutStockQty ,case when os.OutStockNumber is not null then '出库单' end as type " +
                    "from t_OutStock os,t_OutStockEntry oe,t_Supplier sp,t_Storage sg,t_Product pd " +
                    "where os.OutStockNumber =oe.OutStockNumber and oe.StorageID =sg.StorageID and oe.ProdID =pd.ProdID and oe.SupplierID =sp.SupplierID " + sqlcase2;
            DataSet dt = ds.GetRecord(sqlCmd);
            return dt.Tables[0];

        }
        /// <summary>
        /// 删除指定日期盘点数据 
        /// </summary>
        /// <returns>布尔值</returns>
        public static bool DelStockTaking(string timefrom)
        {
            string sqlCmd;
            sqlCmd = "delete t_StockTaking where InputDate='"+timefrom +"'";
            return ds.ExecuteSQL(sqlCmd);
        }
       
        /// <summary>
        /// 通过入库单获取入库日期 
        /// </summary>
        /// <returns>日期string格式值</returns>
        public static string GetInDateByNumber(string instocknumber)
        {
            string result = "";
            string sqlCmd;
            sqlCmd = "select InStockDate from t_InStock where InStockNumber='"+instocknumber +"'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToDateTime (dr[0]).ToString ("yyyyMM");
            }
            return result;
        }
        /// <summary>
        /// 通过出库单获取出库日期 
        /// </summary>
        /// <returns>日期string格式值</returns>
        public static string GetOutDateByNumber(string outstocknumber)
        {
            string result = "";
            string sqlCmd;
            sqlCmd = "select OutDate from t_OutStock where OutStockNumber='" + outstocknumber + "'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToDateTime(dr[0]).ToString("yyyyMM");
            }
            return result;
        }
        /// <summary>
        /// 通过调整单获取调整日期 
        /// </summary>
        /// <returns>日期string格式值</returns>
        public static string GetExDateByNumber(string exstocknumber)
        {
            string result = "";
            string sqlCmd;
            sqlCmd = "select ExDate from t_ExchangeStock where ExStockNumber='" + exstocknumber + "'";
            DataTableReader dr = Ds.GetRecord(sqlCmd).CreateDataReader();
            if (dr.Read())
            {
                result = Convert.ToDateTime(dr[0]).ToString("yyyyMM");
            }
            return result;
        }
    
        #endregion

        /**/
        /// 名称：IsNumberic 
        /// 功能：判断输入的是否是整数 
        /// 参数：string oText：源文本 
        /// 返回值： bool true:是 false:否 
        /// </summary> 
        public bool IsNumberic(string oText)
        {
            try
            {
                int var1 = Convert.ToInt32(oText);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, SqlParameter[] cmdParms, SqlConnection connection, SqlCommand cmd)
        {
            
             
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
              
            }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString,  SqlParameter[] cmdParms,SqlConnection connection, SqlCommand cmd)
        {
           
             
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
              
            
        }



        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, SqlCommand cmd)
        {

            cmd.CommandText = SQLString;
                DataSet ds = new DataSet();
                try
                {
                    SqlDataAdapter command = new SqlDataAdapter(cmd);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;

        }

         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

     }
}
